Help! Query Group By not Working correctly (1 Viewer)

gonzaje20

New member
Local time
Today, 05:45
Joined
Oct 4, 2020
Messages
1
I am developing a database to keep track of downtime for my machines. The operators will enter any downtime via form and then everyday I sum up the downtime via query and compare this to the computer program’s calculated downtime. The computer program gives me two totals: one for dayshift and one for night shift. Likewise, the operators also have a night and day shift schedule.
the issue is night shift... the computer programs numbers for night and day shift have the same date. However, the night shift (6pm-6am) operator may record downtime in the early morning. This should be summed up with the previous day’s downtime. I circumvented this by creating a query (corrected time query)that subtracts 6 hrs to each entry:
Example:

Night shift 10–1-2020
operator enters 10/2 1am-2am
Query subtracts- 10/1 7pm-8PM

When I group this field in another query based on day, any operator entered records for 10/1 are grouped together but those on 10/2 are grouped separately even though the date field in the corrected time query show the same dates....
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:45
Joined
Sep 21, 2011
Messages
7,717
Create a function to give the date you require.
Identify if the time is in the night shift, then adjust the date accordingly.?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Feb 19, 2002
Messages
31,026
Are you sure that time is not part of the date field. Formatting a date at the table level will hide times but does not remove them. NEVER, EVER format data at the table level if the formatting hides anything. All that does is confuse you. Do your formatting in queries or on forms/reports.
 

Users who are viewing this thread

Top Bottom