Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    Arlington, TX
    Posts
    15

    Unanswered: 90 Day Rolling Query

    I'm trying to build an Attendance Tracking System that has entries with occurances (the type of occurance, the date of the occurance and other minor details). Our policy states that if you are absent and absent again before 90 days is up, then you move up to the next level of discipline. Then the 90 days resets for the second occurance. If you get another occurance within 90 days of the second occurance it goes to again the next step of discipline. Anybody have any ideas of how I can check to see if a previous entry is within 90 days of the next record?

    I need to have the query appear sort of like this

    OccDate PreviousOccDate TotalDays
    6/1/03 3/1/03 92
    7/1/03 6/1/03 30
    8/1/03 7/1/03 31

    I tried using the DLookUp Function to get the previous date, but with no luck!

    This is what I used to get the previous date:

    DLookUp("MAX([Date])","tbl_AttendanceAbs*","[tbl_AttendanceAbs*]![Date] < " & [tbl_AttendanceAbs*]![Date] & " AND [EMP_ID]=" & [tbl_AttendanceAbs*]![EMP_ID])

    EMP_ID is the particular Employee and Date is the date the occurance took place

    It always returns a Null Set for some reason...

  2. #2
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55

    Re: 90 Day Rolling Query

    Sounds like you work in a call center. I think i know what your looking for.

    Do you just want to know the last occurance and then see if it is 90 days from the query?

    If so you can do a sum query:

    SELECT tblOccurance.emp_id, Last(tblOccurance.NewOccDate) AS LastOfNewOccDate, Last(tblOccurance.PreOccDate) AS LastOfPreOccDate
    FROM tblOccurance
    GROUP BY tblOccurance.emp_id;


    Once you run that query then run another query to compare the two dates and get your number of days between functions.


    Originally posted by sh4k3s
    I'm trying to build an Attendance Tracking System that has entries with occurances (the type of occurance, the date of the occurance and other minor details). Our policy states that if you are absent and absent again before 90 days is up, then you move up to the next level of discipline. Then the 90 days resets for the second occurance. If you get another occurance within 90 days of the second occurance it goes to again the next step of discipline. Anybody have any ideas of how I can check to see if a previous entry is within 90 days of the next record?

    I need to have the query appear sort of like this

    OccDate PreviousOccDate TotalDays
    6/1/03 3/1/03 92
    7/1/03 6/1/03 30
    8/1/03 7/1/03 31

    I tried using the DLookUp Function to get the previous date, but with no luck!

    This is what I used to get the previous date:

    DLookUp("MAX([Date])","tbl_AttendanceAbs*","[tbl_AttendanceAbs*]![Date] < " & [tbl_AttendanceAbs*]![Date] & " AND [EMP_ID]=" & [tbl_AttendanceAbs*]![EMP_ID])

    EMP_ID is the particular Employee and Date is the date the occurance took place

    It always returns a Null Set for some reason...

  3. #3
    Join Date
    Aug 2002
    Location
    Arlington, TX
    Posts
    15

    Re: 90 Day Rolling Query

    You got the idea and yes, it is a Call Center!

    The problem though, is I don't have a field in the table for the previous occurance date. They just changed the Attendance policy and I have to make do with the old format of the tables. You get what I'm saying?


    Originally posted by suthngin
    Sounds like you work in a call center. I think i know what your looking for.

    Do you just want to know the last occurance and then see if it is 90 days from the query?

    If so you can do a sum query:

    SELECT tblOccurance.emp_id, Last(tblOccurance.NewOccDate) AS LastOfNewOccDate, Last(tblOccurance.PreOccDate) AS LastOfPreOccDate
    FROM tblOccurance
    GROUP BY tblOccurance.emp_id;


    Once you run that query then run another query to compare the two dates and get your number of days between functions.

  4. #4
    Join Date
    Aug 2002
    Location
    Arlington, TX
    Posts
    15

    90 Day Rolling Query (HELP, MY BRAIN IS GOING TO EXPLODE!)

    I'm gonna try this again

    I have a query joining multiple tables to get the following fields

    EmployeeName, RecordID, EmployeeID, Supervisor, DateOfOccurance, ExceptionCode, StartTime, EndTime, HoursMinutes, Comments

    They are sorted by EmployeeName then by Date

    I need a way to check the previous DateOfOccurance and see if that is within 90 days of the current DateOccurance for each record and for the query to Group the Occurances by the Rolling 90 Days

    For instance

    EmployeeName is A

    OccuranceDate 1 is 1/1/03
    OccuranceDate 2 is 2/1/03
    OccuranceDate 3 is 3/1/03
    OccuranceDate 4 is 8/1/03
    OccuranceDate 5 is 8/5/03
    OccuranceDate 6 is 8/6/03
    OccuranceDate 7 is 9/1/03

    I need the report to show

    First Rolling 90 Day Period

    1/1/03 - No previous Occurance
    2/1/03 - Previous Occurance 31 Days
    3/1/03 - Previous Occurance 28 Days

    Second Rolling 90 Day Period

    8/1/03 - Previous Occurance 153 Days (Over 90 days new group)
    8/5/03 - Previous Occurance 4 Days
    8/6/03 - Previous Occurance 1 Days
    9/1/03 - Previous Occurance 26 Days


    Any ideas?

  5. #5
    Join Date
    Feb 2002
    Posts
    403
    Make a cross tab query with the row headings as you like. In the value field of the cross tab use Partition function to break the data down into equal date size bites out to 90 days. The partition function looks like:

    Patition(Now() - [myDate], 1, 120,30)

    Now-[myDate] is a long value to evaluate

    1 a long as the starting value
    120 as the end of the range
    30 is the interval to group on

    This will give you a count value of the parameters specified. From there you should be able to work it out.

    HTH

  6. #6
    Join Date
    Aug 2002
    Location
    Arlington, TX
    Posts
    15
    The only problem with that though is that is basing the rolling 90 days off of Now(), but I have to base it on the current occurance date and check and see if the occurance before that is within 90 days of the current one. This has to be done with each record. and if there is a break where the date is not within 90 days, it gets grouped seperate as a new string of rolling 90 days based on the previous occurance.

    Originally posted by dynamictiger
    Make a cross tab query with the row headings as you like. In the value field of the cross tab use Partition function to break the data down into equal date size bites out to 90 days. The partition function looks like:

    Patition(Now() - [myDate], 1, 120,30)

    Now-[myDate] is a long value to evaluate

    1 a long as the starting value
    120 as the end of the range
    30 is the interval to group on

    This will give you a count value of the parameters specified. From there you should be able to work it out.

    HTH

  7. #7
    Join Date
    Feb 2002
    Posts
    403
    Now-[myDate] is a long value to evaluate

    This can be any long value. All you need to do is make the appropriate long value and away it goes.

Posting Permissions

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