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")

Shift Start	Shift End	Off Start	Off End	DAY OFF START	DAY OFF END
6:30	18:30	6:30	12:30	Sunday	Sunday
6:30	18:30	6:30	12:30	Saturday	Saturday
9:00	18:00	9:00	18:00	Wednesday	Wednesday
6:00	18:00	6:00	18:00	Monday	Monday
6:00	18:00	6:00	18:00	Thursday	Thursday
6:00	18:00	6:00	18:00	Sunday	Sunday
6:00	18:00	6:00	18:00	Sunday	Sunday
12:30	0:30	12:30	0:30	Friday	Saturday
12:30	0:30	12:30	0:30	Friday	Saturday
12:30	0:30	12:30	0:30	Sunday	Monday
18:30	6:30	18:30	22:00	Sunday	Sunday
18:30	6:30	18:30	0:30	Tuesday	Wednesday
18:00	3:00	18:00	3:00	Tuesday	Wednesday
18:00	6:00	18:00	6:00	Friday	Saturday
18:00	6:00	18:00	6:00	Saturday	Sunday
18:00	6:00	18:00	6:00	Friday	Saturday
0:30	12:30	0:30	12:30	Monday	Monday
0:30	12:30	0:30	12:30	Tuesday	Tuesday
0:30	12:30	0:30	12:30	Wednesday	Wednesday

<tbody>
</tbody>

Any advice is greatly appreciated. Thank you.

 

[Micron]

Since you only care about the situation when you run this "(the current time I run the query)", why does the check not work when you run it before midnight (Tuesday) or after (Wednesday)? I would think you'd get the same result if somebody is not in. Otherwise, you may need to use date functions to add/subtract hours to your date, but I suspect the only date data you have to work with is Date(), which is the current date and not some point in time of your choosing. You appear to be formatting your query criteria in a way that permits you to compare it to text day values (not a good idea), and you can't add or subtract hours to text data. Perhaps you could use spinner controls on your form to add/subtract hours to Date() so that you could move back/ahead against the clock, but you'd have to build a date with that and use the same approach as you already have.

P.S. I can't follow your reasoning on the table you posted. Perhaps if you coloured the text that corresponds with your assessments of who's not in, it would be easier to grasp.

 

[SFGiants21256]

Thank you for the reply. I'll try to clear up the questions you had.

The reason the time itself matters more than the date is when we have peoplewho are not at work and their regular shift crosses from Sunday to Monday as anexample. With the current setup of being able to filter the query based on theday off then when it switches days the query will now think you are supposed tobe at work (because the current day is Monday and not your off day of Sunday).So I'm trying to figure out how to account for that roll over after midnight.

I have all of the shift times as date/time fields. I tried using "WHEREFormat(Time(),"Short Time") BETWEEN ([ALL].[Off Start]) AND([ALL].[Off End])" to get it to check the times.

Below is a revised table which has the people who should be off on Sunday. If Iwas to run the query at 1000 then I should have the green and red highlightslisted on an "off duty" query and the others on and "onduty" query. If I ran the same report at 1400 the green person should notbe on the "On duty" query and the blue person should still be on the"off duty" query.

In words I'm trying to do something to check the current time to determine ifsomeone is supposed to be at work right now or at home. If they are supposed tobe at work, then check to see if it is their off day and the current time isduring their off time. If it is their off day, then they will be counted as"off duty". If it is not their off day, they will be "onduty".

I hope this clarifies things. Thank you again. <o></o>

Shift Start<o></o>	Shift End<o></o>	Off Start<o></o>	Off End<o></o>	DAY OFF START<o></o>	DAY OFF END<o></o>
6:30<o></o>​	18:30<o></o>​	6:30<o></o>​	12:30<o></o>​	Sunday<o></o>	Sunday<o></o>
6:30<o></o>​	18:30<o></o>​	6:30<o></o>​	12:30<o></o>​	Saturday<o></o>	Saturday<o></o>
9:00<o></o>​	18:00<o></o>​	9:00<o></o>​	18:00<o></o>​	Wednesday<o></o>	Wednesday<o></o>
6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	Monday<o></o>	Monday<o></o>
6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	Thursday<o></o>	Thursday<o></o>
6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	Sunday<o></o>	Sunday<o></o>
6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	Sunday<o></o>	Sunday<o></o>
12:30<o></o>​	0:30<o></o>​	12:30<o></o>​	0:30<o></o>​	Friday<o></o>	Saturday<o></o>
12:30<o></o>​	0:30<o></o>​	12:30<o></o>​	0:30<o></o>​	Friday<o></o>	Saturday<o></o>
12:30<o></o>​	0:30<o></o>​	12:30<o></o>​	0:30<o></o>​	Sunday<o></o>	Monday<o></o>
18:30<o></o>​	6:30<o></o>​	18:30<o></o>​	22:00<o></o>​	Saturday<o></o>	Saturday<o></o>
18:30<o></o>​	6:30<o></o>​	18:30<o></o>​	0:30<o></o>​	Tuesday<o></o>	Wednesday<o></o>
18:00<o></o>​	3:00<o></o>​	18:00<o></o>​	3:00<o></o>​	Tuesday<o></o>	Wednesday<o></o>
18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	Friday<o></o>	Saturday<o></o>
18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	Saturday<o></o>	Sunday<o></o>
18:00<o></o>​	6:00<o></o>​	18:00<o></o>​	6:00<o></o>​	Friday<o></o>	Saturday<o></o>
0:30<o></o>​	12:30<o></o>​	0:30<o></o>​	12:30<o></o>​	Monday<o></o>	Monday<o></o>
0:30<o></o>​	12:30<o></o>​	0:30<o></o>​	12:30<o></o>​	Tuesday<o></o>	Tuesday<o></o>
0:30<o></o>​	12:30<o></o>​	0:30<o></o>​	12:30<o></o>​	Wednesday<o></o>	Wednesday<o></o>

<tbody> </tbody>

<o> </o>

 

[Micron]

This is still a bit difficult for me to grasp, but here goes anyway.
First, if you have all the shift & off start/end times as date-time fields, I don't see the need to format anything when using it in a query. Formatting makes a value look a certain way within the boundaries of the options for a data type - you do not change the value by changing its appearance. It is what it is in the query regardless of how you make it look. Dates are serial numbers, 04/04/2015 is 42109 without the decimal and HH MM SS portion.

I don't see where the people come into this (no indicator whether or not they are supposed to be in) so I will have to guess at something and assume you have that part figured out. My suggestion is WHERE Time() BETWEEN [ALL].[Off Start] AND DateAdd("n",1440,[ALL].[Off End])
06/10/2015 at some moment of writing this as a date-time is 2165.94288, which is not the same as 06/10/2015 (2165) so I add 60 minuteso to extend it to midnight.
Hope that helps.

 

[SFGiants21256]

Thanks again. I'll see if this way explains it better. Using the two examples below.

Person 1 if works 0630-1830 Monday - Saturday. On Sunday, Person 1 only works from 1230 Sunday -1830 Sunday. If the time is 0800 on any day other than Sunday Person 1 should be reported as "At work". On Sunday at 0800 Person 1 should be "off duty". Person 2 works from 1230 - 0030 everyday execpt 1230 Sunday to 0030 Monday. So person 2 should be on duty at 2200 every day except Sunday night. Another example would be at 0010 everyday except Monday morning this person should be a work.

I'm not familiar with what the "DateAdd("n",1440,)" does. Would it matter what the default date/time of the field is for the calculation?

Shift Start<o></o>	Shift End<o></o>	Off Start<o></o>	Off End<o></o>	DAY OFF START<o></o>	DAY OFF END<o></o>
6:30<o></o>​	18:30<o></o>​	6:30<o></o>​	12:30<o></o>​	Sunday<o></o>	Sunday

<tbody>
</tbody>

12:30<o></o>​

0:30<o></o>​

12:30<o></o>​

0:30<o></o>​

Sunday<o></o>

Monday<o></o>

<tbody>


</tbody>

 

[Micron]

If you have "Between 02/01/2015 and 02/10/2015" as criteria for a date field, records for 02/10/2015 will be returned because the end date is inclusive in a Between comparison. However, if it is a date-time field and you do not specify a time in the criteria, the date defaults to 02/10/2015 00:00:00. Therefore, no records are returned for anything after midnight on the end date. DateAdd does just that. My example should add the interval = minutes and 1440 is how many there are in a day. The end date becomes 02/11/2015 00:00:00. If you have not, use the expression I wrote in your query.

 

[SFGiants21256]

I tried that expression and it didn't work. It didn't filter any results. I did try Time() Between [ALL].[Shift Start] And [ALL].[Shift End] and Time() Between [Off Start] And [Off End]. Both results filtered it to the same people (14 of 174) but it was not correct. I added a <> to the front of them and I was able to get the inverse result.

 

[SFGiants21256]

I did remove all formatting from the table and query.

 

[Micron]

Doesn't sound like I helped much, but it sounds like you got what you needed eventually.

 

[SFGiants21256]

It's not filtering correctly for some reason. Here is the SQL code.

SELECT [ALL].[FIRST NAME], [ALL].[Shift Start], [ALL].[Shift End], [ALL].[Off Start], [ALL].[Off End], [ALL].[DAY OFF START], Time() & " " & Date() AS CurrentDate, DateAdd("n",1440,[ALL].[Off End]) AS [Date Adding], Time() AS [Currently On]
FROM [ALL]
WHERE (((Time()) Not Between [Shift Start] And [Shift End]));

I'm trying to figure out how to upload a picture of the query results. It's just odd with how it is calculating it and determining the between. If I understood that more I could probably figure it out but the selection is not consistent. For example, it filters to everyone from one section so they are all marked present even though half of them are supposed to be off.

I appreciate all of your assistance.