Attendances query in Microsoft Access

Question

this is my first time posting a question on this forum. I have been struggling with writing a query in Microsoft Access for a week now and I hope someone here can help me out. I am building a time attendance application using fingerprint in vb6.

The table looks like this:

https://i.sstatic.net/MZcwI.png

As you can see in the table an employee can check in and out more than 2 times a day. My question is: How can i determine in the OriginType column which row is IN or Out? When a employee checks in for the first time the OriginType should be "I". When he checks in for the second time the OriginType should be "O". When he checks in for the 3th time the OriginType should be "I" again and so on.

2nd question which is different from the last one.

I want to write a query that selects from the timeInOut column. I would like the table to look like this:

https://i.sstatic.net/GgAhx.png

As you can see there are 2 new columns now and there's no OriginType column anymore. I still want to use the correlated subquery and the modulus operator. When it's a checkin i want it to be placed in the column "CheckIn" and if it's a checkout i want it to be placed in the column "CheckOut".

Answer 1

You can use a correlated subquery and the modulus operator for this:

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

This query works in the following way:

The subquery gets the amount of rows for that employee that have been posted on the same date as the current row. Then, we calculate the modulus of 2 of that count, returning 1 if the count is not divisible by 2 (e.g. the 1st, 3rd, 5th etc check-in), and 0 if it's not.

If the count is divisible by 2, then it must be a check in, if it's not, it's a check out.