Need help with this query

[EileenJohn]

Hi, I feel like I'm so so dumb. Stuck with this data for weeks. I need to find out total staff that is still in the building.
Staff ID Staff Name Date Time Code.
1432 Rebecca 11/17/2016 7.15am IN
1431 Stanly. 11/17/2016. 7.30am. IN
1432 Mary 11/17/2016. 7.31am. IN
1455. Rose 11/17/2016. 7.45am. IN
1431 Stanly. 11/17/2016. 6.30pm. OUT
1431. Stanly. 11/17/2016. 7.30pm. IN
1432. Rebecca. 11/17/2016. 7.32pm. OUT
Any idea? How to get the total staff still in office and who ? Thanks in advance.

 

[bob fitz]

You should be able to set the criteria in a query of the IN/OUT column to IN to return just the records of those still in.
You would need to create a Totals query to count those still in.

 

[CJ_London]

you need to find the latest record based on the time, and count those that are in.

- query for latest record for each employee - call it qry1

 SELECT StaffID, Max(DateTime) as Latest
 FROM myTable
 GROUP BY StaffID

-query for who's still in

 SELECT T.*
 FROM myTable T INNER JOIN Qry1 Q ON T.StaffID=Q.StaffID AND T.DateTime=Q.DateTime
 WHERE Code="IN"

or just a count

 SELECT Count(*) AS TtlIn
 FROM myTable T INNER JOIN Qry1 Q ON T.StaffID=Q.StaffID AND T.DateTime=Q.DateTime
 WHERE Code="IN"

 

[EileenJohn]

Hi, I've tried using this data

Staff ID StaffName Process Shiff Date Time Code
1234 Rebecca SE1 A 11/16/2016 7:05:03 AM IN
1345 Tom SE3 B 11/17/2016 7:30:30 AM IN
1234 Rebecca SE1 A 11/17/2016 7:32:01 AM OUT
1346 Tom SE3 B 11/17/2016 7:35:00 AM OUT
1345 Drake SE2 C 11/17/2016 7:45:00 AM IN
1234 Rebecca SE1 A 11/17/2016 7:46:00 AM IN
1345 Drake SE2 C 11/17/2016 7:48:00 AM IN
1347 Frank SE1 A 11/17/2016 7:50:00 AM IN
1348 Mary SE1 A 11/17/2016 8:45:00 AM IN
1348 Mary SE1 A 11/17/2016 6:00:00 PM OUT

My query code :
SELECT Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Max(Attendance.Date) AS MaxOfDate, Max(Attendance.Time) AS MaxOfTime, Attendance.Code
FROM Attendance
GROUP BY Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Code;

Result:
Staff ID StaffName Process Shiff MaxOfDate MaxOfTime Code
1234 Rebecca SE1 A 11/17/2016 7:46:00 AM IN
1234 Rebecca SE1 A 11/17/2016 7:32:01 AM OUT
1345 Drake SE2 C 11/17/2016 7:48:00 AM IN
1345 Tom SE3 B 11/17/2016 7:30:30 AM IN
1346 Tom SE3 B 11/17/2016 7:35:00 AM OUT
1347 Frank SE1 A 11/17/2016 7:50:00 AM IN
1348 Mary SE1 A 11/17/2016 8:45:00 AM IN
1348 Mary SE1 A 11/17/2016 6:00:00 PM OUT

What I really want is (Who's Still in):

Staff ID StaffName Process Shiff Date Time Code
1234 Rebecca SE1 A 11/17/2016 7:46:00 AM IN
1345 Drake SE2 C 11/17/2016 7:48:00 AM IN
1347 Frank SE1 A 11/17/2016 7:50:00 AM IN

Any idea? Please help...

 

[CJ_London]

why don't you try the query I provided - compare it with yours

 

[jdraw]

What is the explanation for

1345 Drake SE2 C 11/17/2016 7:45:00 AM IN

IN twice but never OUT??

Do you do any validation (smell test) of the records? Should you?

 

[EileenJohn]

why don't you try the query I provided - compare it with yours

Hi, I've tried:
SELECT Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Date, Attendance.Time
FROM Attendance LEFT JOIN qry1 ON (Attendance.Time = qry1.MaxOfTime) AND (Attendance.Date = qry1.MaxOfDate) AND (Attendance.[Staff ID] = qry1.[Staff ID])
WHERE (("Code"='IN'))
GROUP BY Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Date, Attendance.Time;

But, the result is empty...

 

[EileenJohn]

What is the explanation for

1345 Drake SE2 C 11/17/2016 7:45:00 AM IN

IN twice but never OUT??

Do you do any validation (smell test) of the records? Should you?

Hi, B'cos sometime the scan device will come out with "Card can't be read" when the staff scan their card. So the staff will try to re-scan their card. Tht's why some of the data (IN/OUT) came twice.
Well, I'm just need to download the transaction report from the time management system and make a daily report.

 

[EileenJohn]

Hi, I've tried:
SELECT Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Date, Attendance.Time
FROM Attendance LEFT JOIN qry1 ON (Attendance.Time = qry1.MaxOfTime) AND (Attendance.Date = qry1.MaxOfDate) AND (Attendance.[Staff ID] = qry1.[Staff ID])
WHERE (("Code"='IN'))
GROUP BY Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Date, Attendance.Time;

But, the result is empty...

Hi, Thank you sooooooo much CJ_London my problem solved.