sql - Time Attendances query in Microsoft Access

Question

I'm having troubles with writing a query in Microsoft Access. This is how my table looks like and where i want to retrieve data from:

I want to write a query that has the following result:

As you can see in the first table an employee can check IN and OUT more than 2 times a day. When a employee checks in for the first time the Date/time should be placed in the first colum "CheckIn". When he checks in for the second time the Date/time should be placed in the second column "CheckOut". When he checks in for the 3th time the Date/time should be placed in the column "CheckIn" and so on.

I have learned from my previous question that I can use a subquery and the modulus operator for a similar situation like this. But I can't figure out how i can make the query work for the problem above.

Answer 1

Let's start with the answer from the previous question, and work our way from there.

This query defines if it's a check in, or check-out. Let's call it qryCheckInOut

SELECT EmployeeID, 
    timeInOut, 
    IIF(
        (SELECT COUNT(*) 
        FROM MyTable s 
        WHERE s.EmployeeID = m.EmployeeID 
        AND s.timeInOut <= m.timeInOut
        AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1, "I", "O") As OriginType
FROM MyTable m

Then, we can get the check-ins from that query, and use a subquery to get the check-outs.

We use conditions to make sure the check out is on the same day, and later than the check in, and use the Min aggregate to make sure it's the next time (the lowest possible time).

SELECT q.EmployeeID, 
    q.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM qryCheckInOut s
    WHERE s.EmployeeID = q.EmployeeId
    AND s.TimeInOut > q.TimeInOut
    AND s.TimeInOut <= Int(q.TimeInOut) + 1) As TimeOut
FROM qryCheckInOut q
WHERE q.OriginType = 'I'

Note that, in the subquery of the second query, you don't need to check if it's a check in or check out, since the lowest time higher than the check in on the same day always is a check out.

If you want to do it in a single query, you can use the query below. However, it will be substantially harder to debug

SELECT m.EmployeeID, 
    m.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM MyTable s
    WHERE s.EmployeeID = m.EmployeeId
    AND s.TimeInOut > m.TimeInOut
    AND s.TimeInOut <= Int(m.TimeInOut) + 1) As TimeOut
FROM MyTable m
WHERE 
    (SELECT COUNT(*) 
    FROM MyTable s 
    WHERE s.EmployeeID = m.EmployeeID 
    AND s.timeInOut <= m.timeInOut
    AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1