Well let me explain what I have and maybe you can help me out in the same
terms or give me any feedback on how I should go about doing this...
I have a table set up for ID#, Activity, Clock-In (check box), Clock-Out
(check box), and Timestamp (=now()). How can I take each entry of the
timestamp and figure out the difference in time to calculate the wage? The
main issue is getting the calculations for total hours worked in a day, week,
month.
ok... I thought that the clockout and clockin times were in the same
record. If you need to pull them from different records it's a bit
harder!
You'll need a "Self Join" query to pair each clockin with its
corresponding clockout. This could be tricky - there's nothing in the
database to prevent someone from clocking in Monday morning, not
clocking out at all, clocking in again Tuesday, and clocking out
Tuesday afternoon. This simple query will fail in that case (it will
actually give the employee credit for 32 hours on Monday and 8 hours
on Tuesday for example):
SELECT Table.Timestamp AS TimeIn, Table_1.Timestamp As TimeOut,
DateDiff("n", Table.Timestamp, Table_1.Timestamp) AS TimeOnJob
FROM Table INNER JOIN Table AS Table_1
ON Table.[ID#] = Table_1.[ID#]
WHERE Table.ClockIn = True
AND Table_1.ClockOut = True
AND Table_1.Timestamp = (SELECT Min(X.[Timestamp]) FROM Table AS X
WHERE X.[ID#] = Table.[ID#] AND X.ClockOut = True AND X.Timestamp >
Table.Timestamp)
WHERE Table.Timestamp >= [Enter start date and time:] AND
Table.Timestamp <= [Enter end date and time:]
This rather hairy query (which drastically needs testing, it's off the
top of my head) finds for each Clock_in record the next corresponding
Clock_Out record for that employee, and calculates the time difference
in minutes. Meals and breaks aren't accounted for here, that's another
issue.
John W. Vinson[MVP]
