SELECT EmpId, Min(PUNCHDATETIME) AS MinOfPUNCHDATETIME
FROM YourTableName
GROUP BY EmpId, Format([PUNCHDATETIME],"dd\/mm\/yyyy");
SELECT EmpId, Max(PUNCHDATETIME) AS MaxOfPUNCHDATETIME
FROM YourTableName
GROUP BY EmpId, Format([PUNCHDATETIME],"dd\/mm\/yyyy");
SELECT t1.EmpId, t1.PunchIn, t2.PunchOut
FROM (
SELECT EmpId, DateValue([PUNCHDATETIME]) AS PUNCHDATE, Min(PUNCHDATETIME) AS PunchIn
FROM YourTableName
GROUP BY EmpId, DateValue([PUNCHDATETIME])
) AS t1
LEFT JOIN (
SELECT EmpId, DateValue([PUNCHDATETIME]) AS PUNCHDATE, Max(PUNCHDATETIME) AS PunchOut
FROM YourTableName
GROUP BY EmpId, DateValue([PUNCHDATETIME])
) AS t2 ON t1.EmpId=t2.EmpId AND t1.PUNCHDATE=t2.PUNCHDATE
Good question.
--> what's a punchout when you have more than 2 punches? And what do you do with the other punches?
The first punch time will be punch in and the last punch time will be Punch out.
This is what I need.
Attached database.
SELECT Table1.EmpID, Min(Table1.PUNCHDATETIME) AS Entry, Max(Table1.PUNCHDATETIME) AS Exit
FROM Table1
GROUP BY Table1.EmpID;
Then remove TimeValue from the two expressions.
Open in new window