Determine Worker Present at work at current time

[SFGiants21256]

Hello,

I should start out that I am very very new with access. I've done some VBA in excel but it is nothing extensive.

I'm trying to create a tracker to tell me what employees are present for work at a specific time (the current time I run the query). We are working 6 days a week at 12 hours each shift. I've been able to get the query to filter out people who have the same off day as the current day. The issue I'm having is when someone is off Tuesday at 2300 until Wednesday at 1100. Whenever I crossover midnight I'm messing myself up because then the day of the week is useless. So that is where the time comes into play. I'm trying to build two query's. One which will list everyone present for work and the other will list everyone off. The hardest thing is there are some people who are only taking 6 hours off instead of a full 12 hour shift. The first row in the example below shows this.

Using the example below, if it was 10:00am on Sunday then there should be 3 people off shift. If it was 2:00pm on Sunday it should now be 2 people.

To get the query to filter out the people off on the particular day I used the following formula in the "Criteria" section of the design query:
<>Format(Weekday(Date()),"dddd")
[TABLE="width: 416"]
<tbody>[TR]
[TD]Shift Start
[/TD]
[TD]Shift End
[/TD]
[TD]Off Start
[/TD]
[TD]Off End
[/TD]
[TD]DAY OFF START
[/TD]
[TD]DAY OFF END
[/TD]
[/TR]
[TR]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]18:30
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD]Sunday
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]18:30
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD]Saturday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD="align: right"]9:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]9:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD]Wednesday
[/TD]
[TD]Wednesday
[/TD]
[/TR]
[TR]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD]Monday
[/TD]
[TD]Monday
[/TD]
[/TR]
[TR]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD]Thursday
[/TD]
[TD]Thursday
[/TD]
[/TR]
[TR]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD]Sunday
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD]Sunday
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[/TR]
[TR]
[TD="align: right"]18:30
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]18:30
[/TD]
[TD="align: right"]22:00
[/TD]
[TD]Sunday
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD="align: right"]18:30
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]18:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[/TR]
[TR]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]3:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]3:00
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[/TR]
[TR]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD]Saturday
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD]Monday
[/TD]
[TD]Monday
[/TD]
[/TR]
[TR]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD]Tuesday
[/TD]
[TD]Tuesday
[/TD]
[/TR]
[TR]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD="align: right"]0:30
[/TD]
[TD="align: right"]12:30
[/TD]
[TD]Wednesday
[/TD]
[TD]Wednesday
[/TD]
[/TR]
</tbody>[/TABLE]



Any advice is greatly appreciated. Thank you.

 

[Micron]

Consider uploading a zipped copy of the db. Between that at the info you posted, I might be able to help. No doubt you fully grasp the process and what results you want, but I'm afraid I can't as I don't see what you're dealing with.
Never mind - I came back only to see that you can't upload files, apparently. If you are willing to upload somewhere, I am active on AccessForums.net where you might get more Access related help anyway. They allow uploads. You could send me a PM there so I will know you have posted should you decide to.