Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,
    I have data which stores sickness dates is in the format:
    Employee, Start date, End date

    This records when an employee goes off sick and "comes back to work"

    Unfortunately, the sysyem generates new records if the sickness is over a certain length, so a back to work date of, say, the 5th is followed by another start sick date of the 6th.

    I'll use numbers instead of dates as an example:

    Employee, Start, End
    A,15,17
    A,18,25
    A,26,29
    A,35,37
    A,38,40
    A,45,47

    What I want is a query that will produce:
    A,15,29
    A,35,40
    A,45,47

    This is the three periods of sickness.

    Basically, if the end date of one sickness record is the day before the start date of another sickness record, take the start date of the first record and the end date of the last continuous record.

    There can be up to 52 records for each sickness incident.

    Thanks,
    Jim

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Unfortunately, I don't see a good way to create a query to do what you describe. However with a VBA procedure, you could process the records sequentially based on the start date and check to see if the end date is one less than the next start date, and output a new recordset to a table with the result. You could use either ADO or DAO - my preference would be DAO as it is easier to understand and use. If you want more details or are uncomfortable with VBA, I'm sure one of us could put together a code example.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Coincidentally, I've just been presented with an almost identical situation! And I haven't yet figured out a good way to do it. As Wendell mentioned, it looks like it will have to be done through code. I'm figuring I'll need a working table to hold the output (ID, StartDate, EndDate). In code, I'll then create a recordset from the records in the main table, then work through the records in code to determine when to write a record to the working table. The Working Table with then become the basis for my reports.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    OK,

    I've had a go at the code (I'm a beginner) but I've got some basic stuff wrong.

    Instead of the debug.print line, how do I write the output to a new table?


    Any pointers would help, especially with tidying up the code as I've cobbled it together from various sources.

    Thanks
    Jim
    Code:
    Function concatenatedata()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim EmployeeNumber, EmployeeNumberNext As String
    Dim StartSick, EndSick, nextStartSick, nextEndSick
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT [Source Data].Employee AS EmpNum, [Source Data].From AS FromDate, [Source Data].To AS ToDate FROM [Source Data] ORDER BY [Source Data].Employee, [Source Data].From;", dbOpenForwardOnly)
    
    Do While Not rst.EOF
        EmployeeNumber = rst![EmpNum]
        StartSick = rst![FromDate]
        EndSick = rst![ToDate]
        rst.MoveNext
        
        EmployeeNumberNext = rst![EmpNum]
        nextStartSick = rst![FromDate]
        nextEndSick = rst![ToDate]
        
        Do While (EmployeeNumber = EmployeeNumberNext) And (nextStartSick = EndSick + 1)
            EndSick = nextEndSick
            rst.MoveNext
            nextStartSick = rst![FromDate]
            nextEndSick = rst![ToDate]
        Loop
        Debug.Print EmployeeNumber, StartSick, EndSick
        
        'write the record to the new table Employee Number, StartSick, Endsick
        
    Loop
    
    End Function

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    OK, I'm nearly there with the above code.

    But:
    How do I get the output into a new table?
    How do I stop the "no record" error?


    Jim

    Sample data I'm using for the above:

    Employee From To
    13 1 2
    13 3 6
    13 7 12
    13 16 17
    13 22 25
    13 26 29
    13 34 45
    17 25 26
    17 27 29
    17 35 37

    Immediate Output:
    13 1 12
    13 16 17
    13 22 29
    13 34 45
    17 25 29

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm stumped at the last hurdle.

    Why am I being prompted for a value of "EmployeeNumber"?
    It's to do with the syntax I've used in:
    "INSERT INTO [Concatenated Data] ( Employee, [From], [To] ) Values (EmployeeNumber,StartSick,EndSick);"

    Please, any ideas anyone?

    Jim
    Code:
    Function concatenatedata()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim EmployeeNumber, EmployeeNumberNext, WriteSQL As String
    Dim StartSick, EndSick, nextStartSick, nextEndSick
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT [Source Data].Employee AS EmpNum, [Source Data].From AS FromDate, [Source Data].To AS ToDate FROM [Source Data] ORDER BY [Source Data].Employee, [Source Data].From;", dbOpenForwardOnly)
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [Concatenated Data]"
    DoCmd.SetWarnings True
    
    Do While Not rst.EOF
        EmployeeNumber = rst![EmpNum]
        StartSick = rst![FromDate]
        EndSick = rst![ToDate]
        rst.MoveNext
        
        EmployeeNumberNext = rst![EmpNum]
        nextStartSick = rst![FromDate]
        nextEndSick = rst![ToDate]
        
        Do While (EmployeeNumber = EmployeeNumberNext) And (nextStartSick = EndSick + 1)
            EndSick = nextEndSick
            rst.MoveNext
            nextStartSick = rst![FromDate]
            nextEndSick = rst![ToDate]
        Loop
        WriteSQL = "INSERT INTO [Concatenated Data] ( Employee, [From], [To] ) Values (EmployeeNumber,StartSick,EndSick);"
        DoCmd.RunSQL WriteSQL
        'write the record to the new table Employee Number, StartSick, Endsick
        
    Loop
    
    End Function

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Got it:

    WriteSQL = "INSERT INTO [Concatenated Data] ( Employee, [From], [To] ) Values (" & EmployeeNumber & " ," & StartSick & " ," & EndSick & " );"

    Now it just fails at the end but I don't care as it's done its work by then!

    Still, any help in tidying up the code would be appreciated as I'm sure it can be greatly improved.

    Thanks,
    Jim

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 981 Times in 911 Posts
    Does it fail at the end because rst.MoveNext is in the middle of the code instead of the last statement before the loop? You should do the work using the current record set, then MoveNext and test for EOF.

    cheers, Paul

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Do While (EmployeeNumber = EmployeeNumberNext) And (nextStartSick = EndSick + 1)
    Should this be?

    Code:
    Do While (EmployeeNumber = EmployeeNumberNext) And (nextStartSick = EndSick + 1) and not rst.EOF
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks for both of these!

    Now I've use it for real data instead of just numbers I'm getting a strange problem.

    The output I was expecting for my own sickness record was:


    00299 25/01/2001 01/02/2001
    00299 13/03/2001 13/03/2001
    00299 19/04/2001 30/04/2001
    00299 27/03/2003 31/03/2003
    00299 27/01/2005 09/02/2005
    00299 24/10/2005 24/10/2005
    00299 15/12/2005 15/12/2005
    00299 10/10/2006 11/10/2006
    00299 16/07/2008 16/07/2008
    00299 17/07/2008 17/07/2008

    What I'm getting is:

    Employee From To
    299 00:17:59 00:00:22
    299 00:03:07 00:03:07
    299 00:03:25 00:05:24
    299 00:06:28 00:07:26
    299 00:19:23 00:03:14
    299 00:01:43 00:01:43
    299 00:00:54 00:00:54
    299 00:00:43 00:00:47
    299 00:01:38 00:01:44

    I think what's happening is it's taking the day, dividing it by the month, then dividing it by the year, then showing the result as a date/time field.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't know what difference it would make, but the thing I notice is your variable declarations

    Dim EmployeeNumber, EmployeeNumberNext As String
    Dim StartSick, EndSick, nextStartSick, nextEndSick
    Under these EmployeeNumberNext is a string, and all the rest are variants.

    I would declare each variable on its own line with the required type

    Code:
    Dim EmployeeNumber as String
    Dim EmployeeNumberNext As String
    Dim StartSick as Date
    Dim EndSick as Date
    Dim nextStartSick as Date
    Dim nextEndSick as Date
    Regards
    John



  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi thanks for the advice on the .eof problem, I'll get to that once I fix the dates being used as formulas problem.

    I'm stumped with this SQL writing numbers when the immediate window is showing strings and dates.

    The destination table is correctly set up with EmployeeNumber, StartSick, EndSick is set up as Text, Date, Date

    But still, the Immediate window shows

    00001, 2/2/2004, 8/2/2004

    Yet the table shows

    1, 00:00:43, 00:02:52

    The 43 seconds comes from 2 divided by 2 divided by 2004 = 1/2004th , 1/2004th of a day = 43 seconds!

    Stumped!
    Jim

    Code:
    Function concatenatedata()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim EmployeeNumber As String
    Dim EmpNum As String
    Dim EmployeeNumberNext As String
    Dim WriteSQL As String
    Dim StartSick As Date
    Dim FromDate As Date
    Dim ToDate As Date
    Dim EndSick As Date
    Dim nextStartSick As Date
    Dim nextEndSick As Date
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT [Source Data].Employee AS EmpNum, [Source Data].From AS FromDate, [Source Data].To AS ToDate FROM [Source Data] ORDER BY [Source Data].Employee, [Source Data].From;", dbOpenForwardOnly)
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [Concatenated Data]"
    
    
    Do While Not rst.EOF
        EmployeeNumber = rst!EmpNum
        StartSick = rst!FromDate
        EndSick = rst!ToDate
        rst.MoveNext
        
        EmployeeNumberNext = rst!EmpNum
        nextStartSick = rst!FromDate
        nextEndSick = rst!ToDate
        
        Do While (EmployeeNumber = EmployeeNumberNext) And (nextStartSick = EndSick + 1) And Not rst.EOF
            EndSick = nextEndSick
            rst.MoveNext
            nextStartSick = rst![FromDate]
            nextEndSick = rst![ToDate]
        Loop
      
        WriteSQL = "INSERT INTO [Concatenated Data] ( Employee, [From], [To] ) Values (" & EmployeeNumber & " ," & StartSick & " ," & EndSick & " );"
        DoCmd.RunSQL WriteSQL
        Debug.Print EmployeeNumber, StartSick, EndSick
        ' The problem is here, the Write command is writing numbers for EmployeeNumber (it should be a string) and the
        ' result of viewing the date as a formula and writing the result of 2 divided by 2 divided by 2004 = 1/2004th instead of the date 2/2/2004
        
    Loop
    DoCmd.SetWarnings True
    End Function

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Does this make any difference?

    Code:
    WriteSQL = "INSERT INTO [Concatenated Data] ( Employee, [From], [To] ) Values (" & chr(34) & format(EmployeeNumber,"0000") & chr(34) & " ,#" & format(StartSick,"mm/dd/yyyy") & "# ,#" & format(EndSick,"mm/dd/yyyy") & "# );"
    Dates in SQL statements need to be in mm/dd/yyyy format, and I notice that you, like me, are somewhere that uses dd/mm/yyyy fortmat.
    Regards
    John



  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Excellent, I'll try that, but it doesn't explain why the EmployeeNumber string gets written as a number to a text field.

    Jim

  15. #15
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Finally sussed it, thanks to this link:

    http://www.webmaster-talk.com/asp-fo...variables.html

    I changed:

    WriteSQL = "INSERT INTO [Concatenated Data] ( Employee, [From], [To] ) Values (" & EmployeeNumber & " ," & StartSick & " ," & EndSick & " );"

    to:

    WriteSQL = "INSERT into [Concatenated Data] (Employee,[From],[To]) Values (" & "'" & EmployeeNumber & "'" & " ," & "'" & StartSick & "'" & " ," & "'" & EndSick & "'" & " );"

    There's a single apostrophe between the two apostrophes"

    Thank to everyone that helped.

    Jim

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •