Time of First and last record in DAY.

[mobbarley]

Hey there.

I'm trying to do something similar to a time clock system for staff, there will be several records in a database each day for a staff member (with a unique ID), and I'd like to find the first and last record each day and calculate the time in between.

I have no idea how to go about this in a query, any help greatly appreceated.

Regards,
JohnR.

 

[lupins46]

What data is on each record?

 

[dzepeda]

Try something like:

SELECT Format(Max([Time])-Min([Time]),"hh:nn") AS Elapsed, Format([Time],"dd-mm-yyyy") AS Day, YourTable.StaffMmember
FROM YourTable
GROUP BY Format([Time],"dd-mm-yyyy"), YourTable.StaffMmember;

This must return the elapsed time between the first and the last hour of each staff member every day. You can make the adjustments to fit your requirements

Greetings from Chile

 

[mobbarley]

Lupins: On each record there will be a primary key, card number, staff number (unique to each staff member), name, and a few other details. This will actually be triggered by a card swipe, releasing a door strike.

 

[lupins46]

I think the other response (dzepeda) will give you the answer you need.

 

[mobbarley]

This code shows #error in the elapsed field, but I believe that could be because the field in the database shows date & time as: 18/03/04 01:36:37pm, and one subtract the other wont work?

Regards
John R.

 

[lupins46]

The datetime format is not relevant.
There must be a typo in the formula you have entered.
If you can't see it yourself then post the sql here.