1

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.stack.imgur.com/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.stack.imgur.com/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".

Tan Nguyen
  • 21
  • 6

2 Answers2

1

You can get the last OriginType from the following query and then insert 'I' if last was "O", or no rows returned by the query and "O" if last was "I". 

SELECT OriginType from Employee where employeeId = 1 and timeInOut = (select max(timeInOut) from Employee where employeeId = 1)
Ari Singh
  • 1,228
  • 7
  • 12
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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • That's a different question. Feel free to ask it, though – Erik A Mar 11 '18 at 12:16
  • Hi Erik, i have posted a new question. It's similar to this question. I'd appreciate it if you could take a look at it. https://stackoverflow.com/questions/49236088/time-attendances-query-in-microsoft-access – Tan Nguyen Mar 12 '18 at 13:10
  • @Tan I will. You could link to this question in your current one. – Erik A Mar 12 '18 at 13:13