Calculate the difference between 2 dates/times on 2 separate records (1 Viewer)

[veraloopy]

I've inherited a clocking in database which enables staff to register an 'In' and 'Out' event based on their fingerprint stamp.

The table has the following fields and creates a record for each fingerprint stamp, so I've got In's and Out's on separate records.

ClockID - Employee number
Name - Persons full name
CheckType - This is either I or O for 'In' or 'Out'
CheckTime - This is in the format dd/mm/yyyy hh:mm


I've been asked to create a report for excel that pulls in the employee's In and Out times for each day so payroll can be processed at the end of the week

The problem is that if someone registers more than 1 In or Out, I don't know how to just use the first stamp In and the last stamp Out to calculate the difference. (Or better still calculate between multiple times throughout the day)

I've tried various things like splitting the date and time into their own columns in the query, but the DateDiff function doesn't work because there is only 1 date/time per record, the other date/time is on a separate record.

This is my query so far...

SELECT USERINFO.Badgenumber, USERINFO.Name, CHECKINOUT.CHECKTYPE, First(IIf([CHECKTYPE]="I",[ChkTime1],"")) AS CLStart, Last(IIf([CHECKTYPE]="O",[ChkTime1],"")) AS CLEnd, DateValue([CHECKTIME]) AS ChkDate, TimeValue([CHECKTIME]) AS ChkTime1
FROM CHECKINOUT LEFT JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID
GROUP BY USERINFO.Badgenumber, USERINFO.Name, CHECKINOUT.CHECKTYPE, DateValue([CHECKTIME]), TimeValue([CHECKTIME]);

However, it puts the In and Out on 2 separate lines and I can't work out how to calculate the daily hours (see attached spreadsheet)

Any help in being able to calculate the daily hours worked would be much appreciated

 

[MarkK]

Rather than the letter "I" and "O" use the numbers 1 and -1 for CheckType.
Then you can very easily add and subtract the dates and times recorded in the current record. Consider...

Debug.Print DSum("CheckType * CheckTime", "CheckInOut", "UserID = " & SomeID & " AND DateValue(CheckTime) = #" & QueryDate & "#")

See what that does? Dead simple.
Maybe you need to use the ABS() function to get the absolute value since a negative date is not that meanigful, but you'll get the diffference anyway.
And this'll work fine if the user punches in and out any even number of times in a given day, and if there is an odd number of punches the result will be so out of range it'll be easy to check for.
Cheers,

 

[veraloopy]

Thanks for your reply on this one ;-)

Where would I put this code? I've tried it as part of the query or running it from a form but keep getting errors

I'm unable to change the 'O' and 'I' as this is coming from the fingerprint program which I can't change. All I have is the table of data to try to manipulate.

I've added a column in the query to change the 'O' to -1 and the 'I' to 1 but then I get errors relating to an invalid expression

Any ideas?

 

[Brianwarnock]

I honestly think that you are going to have to write code for this as you will need to check that you have matching I O, although in Lagbolts scenario the time will be crazy if there is no match, it does not seem a very professional way to go.

 

[Brianwarnock]

As a second thought you can do this , with the proviso mentioned , in a query adopting Lagbolts suggestion by Grouping on ClockID, DatevalueChecktime), and summing
IIF(Checktype ="I",Timevalue(checktime)*-1,timevalue(checktime))

We are also assuming nothing goes over day's end.

Brian

 

[MarkK]

Does not seem very professional? And then you propose the same thing?

 

[Brianwarnock]

Does not seem very professional? And then you propose the same thing?

Read my post properly.

All I said that given the proviso I stated but you didn't that he need not use a domain function which we know not to be very effecient.

Brian

 

[MarkK]

It says 'Debug.Print DSum().' What do you think I mean by Debug.Print? A production solution?
Whatever the case, charging me with lack of professionalism adds nothing to the thread. If you want to dispute information I post, fine, but then either dispute the information or post something better, or at least something different.
Mark