Need help calculating time query

[helpwithforms via AccessMonster.com]

Hi,

I am trying to query some data from my company mainframe for productivity
purposes. The fields in the table I am querying are: OperatorID,
Transaction Time/Date (Includes data and time), Transaction Type, and
Transaction Description.

Here is the way the mainframe works: There is a Transaction for each time
the operator signs in or signs out (transaction F or O) as well as each time
they move product (several different transaction types). An operator can
sign in and out several times throught their shift, for breaks and such.
There are different work shifts as well, and this is where it gets tricky.
The first shift runs from 8AM to 4PM, and the second shift runs from 6PM to
2AM. So the 2nd shift ends on a different day, but the day they clock in is
considered the "shift date." So if they clock in on Monday, but leave on
Tuesday morning at 2AM, the data is for the Monday shift.

What I am wanting to get to is some kind of a summary that would list the
operator ID, Shift Date, Sign on time, Sign off time, Total time worked, and
total number of transactions.

I am having trouble with the time scenarios, because of all the different
transactions. I have a master table with all of the transactions.
The one thing I would like to figure out is the best way to capture the Sign
In/Sign Out transactions for each Shift date for each Operator. I can do a
max/min on the sign in transactions, but how do I get the min for each date
if I have more than one days data in the master table?


Should I break that table down to Sign In transactions, Sign Off transactions
and Other transactions? Also, should I set up Autonumber on the master table?


Thanks for your help
Mike

 

[KARL DEWEY]

I used two queries and a table named Mike_M.

SELECT Mike_M.OperatorID, Format([Mike_M].[TransDate],"m/d/yyyy") AS [Shift
Date], Format([Mike_M].[TransDate],"h:nn AM/PM") AS Clock_In,
Format([Mike_M_1].[TransDate],"h:nn AM/PM") AS Clock_Out, Mike_M.TransDate AS
Clock_Start, Mike_M_1.TransDate AS Clock_Stop,
DateDiff("h",[Mike_M].[TransDate],[Mike_M_1].[TransDate]) & " Hrs " &
DateDiff("n",[Mike_M].[TransDate],[Mike_M_1].[TransDate]) Mod 60 & " Mins" AS
[Hours Worked]
FROM Mike_M INNER JOIN Mike_M AS Mike_M_1 ON Mike_M.OperatorID =
Mike_M_1.OperatorID
WHERE (((Mike_M_1.TransDate)>[Mike_M].[TransDate] And
(Mike_M_1.TransDate)<[Mike_M].[TransDate]+0.75) AND ((Mike_M.Type)="F") AND
((Mike_M_1.Type)="O"))
ORDER BY Mike_M.OperatorID, Format([Mike_M].[TransDate],"m/d/yyyy");


SELECT Mike_M_X.OperatorID, Mike_M_X.[Shift Date], Mike_M_X.Clock_In,
Mike_M_X.Clock_Out, Mike_M_X.[Hours Worked], Count(Mike_M.Type) AS
Transactions
FROM Mike_M INNER JOIN Mike_M_X ON Mike_M.OperatorID = Mike_M_X.OperatorID
WHERE (((Mike_M.TransDate) Between [Clock_Start] And [Clock_Stop]))
GROUP BY Mike_M_X.OperatorID, Mike_M_X.[Shift Date], Mike_M_X.Clock_In,
Mike_M_X.Clock_Out, Mike_M_X.[Hours Worked];