Attendance System - Datetime Sorting

[WinIDS]

Dear all,

I have an attendance database as follows:

tbl_Attendance
Employee_ID
IN/OUT Flag
Datetime

Is it possible to create a query which selects and merges the Datetime according to the IN/OUT Flag? i.e. I want my query to spit out:

qry_Attendance
Employee_ID
Time IN
Time OUT
Date

 

[Peter Reid]

Assuming there was no more than 1 in and out on the same day, you could use a crosstab query, eg.

TRANSFORM First(TimeValue([datetime])) AS InOutTime
SELECT tbl_Attendance.EmployeeID, DateValue([datetime]) AS InOutDate
FROM tbl_Attendance
GROUP BY tbl_Attendance.EmployeeID, DateValue([datetime])
PIVOT tbl_Attendance.[IN/OUT Flag];

If your data has 2 or more ins/outs in a day, you will have to associate the out with an in somehow, be it in tbl_Attendance or some calculated way