Calculating Time From Time Clock Punches in a Single Table 3

[Nickela]

I have a table that contains the following information:

BadgeID - employee's ID #
Punch - Date and Time Stamp
Sequence - 1 (for in) and 2 (for out punches)

There are usually 2 - 3 sets of sequences per day for each employee. I am trying to create a query that calculates the employee's time for that day.

Ex:
Punch: 9/26/2005 8:00 AM Seq: 1
9/26/2005 12:00 PM Seq: 2
9/26/2005 12:30 PM Seq: 1
9/26/2005 4:30 PM Seq: 2

I would expect this to come up with 8 hrs.

I am stuck on how I can calculate this with a query. Any thoughts?

Thank you for your help.

Nickela

 

[Remou]

Hi
Maybe:
[tt]SELECT TableName.BadgeID, TableName.Punch, (SELECT Punch FROM TableName A WHERE A.Sequence=2 AND A.BadgeID=TableName.BadgeID) AS Seq2Time, Format([Punch]-[seq2time],"Short Time") AS Expr1
FROM TableName
WHERE (((TableName.Sequence)=1));[/tt]
The above assumes that the sequence field is numeric.

 

[Nickela]

Thanks, Remou for the tip. When I tried this, I get a message "At most 1 record can be returned from this subquery" and it doesn't show any records. Any thoughts? Thanks, again.

 

[Remou]

Hi
Can you post your SQL? I tested with the information you supplied above:
[tt]
Type: Date / Time Numeric Numeric
Punch Sequence BadgeID
26/09/2005 07:00:00 1 1
26/09/2005 12:00:00 2 1
26/09/2005 12:30:00 1 2
26/09/2005 16:40:00 2 2[/tt]

And it seemed to work ok.

 

[Nickela]

I appreciate your help, Remou.

SELECT tblPunch.BadgeID,
tblPunch.Punch,
(SELECT Punch FROM tblPunch A WHERE A.Seq = 2 AND A.BadgeID = tblPunch.BadgeID) AS Seq2Time,
Format([Punch]-[seq2time],"Short Time") AS Expr1
FROM tblPunch
WHERE (((tblPunch.Seq)=1));

The only difference in my table is that for all of those punches, it is for 1 badge ID. The Employee clocks in, clocks out for lunch, clocks back in, and then clocks back out when they leave.

Any thoughts?

Thanks.

nickela

 

[Remou]

It is the single BadgeID that is causing the problem. Here is another idea you may wish to pick bits from, with some warnings:
- I have only tested very lightly
- If sequence numbers do not run 1,2 1,2 it will cause serious problems.
- Try it first on a scrap database.
SQL:

SELECT tblPunch.Punch, tblPunch.BadgeID, tblPunch.Sequence, NextRec([Punch]) AS Seq2Time, Format([seq2time]-[Punch],"Short Time") AS TimeDiff
FROM tblPunch
WHERE (((tblPunch.Sequence)=1))
ORDER BY tblPunch.Punch;

Function:

Function NextRec(fld) As Date
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From tblPunch Order By Punch"
Set rs = CurrentDb.OpenRecordset(strSQL)

rs.FindFirst "Punch=#" & fld & "#"
rs.MoveNext

NextRec = rs!Punch

rs.Close
Set rs = Nothing
End Function

 

[PHV]

A starting point:
SELECT I.BadgeID, I.Punch AS [In], Min(O.Punch) AS [Out], Format(Min(O.Punch)-I.Punch, 'hh:nn:ss') AS TimeDiff
FROM tblPunch AS I INNER JOIN tblPunch AS O ON I.BadgeID = O.BadgeID
WHERE I.Seq = 1 AND O.Seq = 2 AND I.Punch < O.Punch
GROUP BY I.BadgeID, I.Punch

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsteph]

Nickela,
I have written a timeclock system and I know what you're up against. First, this can't be done efficiently in a single sql statement. I use a stored procedure (it's in sql-server), but for Access, you can just use a code module.

The main issues are that you may not be able to guarantee that the sequence will necessarily be perfect pairs. What if the guy forgets to punch out and you end up an odd number of punches? What if he forgets to both punch out and in for lunch--you have a matched pair of punches, but you're paying him an extra hour (or whatever)

Without getting into too much detail, you need to implement punch-rules in the collection device, such as--two punches in a 60 second period--the second one is ignored, for instance--put up a visible or audible message when that happens. Things like that. You also need to define a time which will be a 'cutoff', when you can collect all punches and only certain special cases (like nite-crews whose times span a day or week boundary) are done manually.

Then you can loop through the ordered recordset, and kick out mismatched pairs for manual adjustment, and sum the good ones, apply OT rules, etc, etc.
--Jim

 

[Nickela]

Thanks everybody. I will try a couple of these things out. I appreciate the help.

Nickela