Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Ohio, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find a day off (2003)

    I have a time keeping database. (Yes, in an old version). It has an employee number, date, start time, planned hours to work (4 fields). (Also, folks using the database work around the clock, a worker could enter a record for 11:30 pm and work 8-10 hr and the database not show a record for a day they were actually here.) The higher ups now want to know the last day a person had off. Right now I have a queries that looks at now(), start time/hrs. worked, now()-1 start time/hrs. worked, now()-2 start time/hrs. worked, and use DateDiff("h" in another query to subtract now()-1 start time/hrs. worked from now() start time/hrs. worked to find out if a person has had 8 hrs off between shift or how many hours worked in a week, etc. But, this is cumbersome (uses many queries) and is messy. Is there an easier way to compare one record to another down through till I have more than 24 hours off between records? Any help would be appreciated. Be gentle with me, I do queries and wizards and a little visual basic but I am not a programmer in visual basic.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a day off (2003)

    You'll need several queries for this anyway, but I don't think VBA will be required. In the attached demo, I created three queries:
    qryTime computes the start and end of a shift as date/time values.
    qryPrev is a totals query that matches qryTime to itself to find the last shift for the same employee prior to the current record; it also calculates the off-time (the time between the end of the last shift and the start of the current one.
    qryOff selects those records from qryPrev for which the off-time is 24 hours or more.

    Does that help?
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Ohio, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a day off (2003)

    Thanks HansV, it looks like you have exactly what I was looking for and a whole lot simpler than where I was headed. Thanks so much, a devoted fan.

Posting Permissions

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