Need help with this query (1 Viewer)

E

EileenJohn

Registered User.
Local time
Today, 12:54
Joined
Nov 23, 2016
Messages
12
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

bob fitz

AWF VIP
Local time
Today, 20:54
Joined
May 23, 2011
Messages
4,727
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.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 20:54
Joined
Feb 19, 2013
Messages
16,629
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

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

-query for who's still in
Code: 
 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
Code: 
 SELECT Count(*) AS TtlIn
 FROM myTable T INNER JOIN Qry1 Q ON T.StaffID=Q.StaffID AND T.DateTime=Q.DateTime
 WHERE Code="IN"
 
E

EileenJohn

Registered User.
Local time
Today, 12:54
Joined
Nov 23, 2016
Messages
12
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...
 
Last edited:
C

CJ_London

Super Moderator
Staff member
Local time
Today, 20:54
Joined
Feb 19, 2013
Messages
16,629
why don't you try the query I provided - compare it with yours
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 15:54
Joined
Jan 23, 2006
Messages
15,385
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?
 
Last edited:
E

EileenJohn

Registered User.
Local time
Today, 12:54
Joined
Nov 23, 2016
Messages
12
CJ_London said:
why don't you try the query I provided - compare it with yours
Click to expand...

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...
 
E

EileenJohn

Registered User.
Local time
Today, 12:54
Joined
Nov 23, 2016
Messages
12
jdraw said:
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?
Click to expand...

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.
 
E

EileenJohn

Registered User.
Local time
Today, 12:54
Joined
Nov 23, 2016
Messages
12
EileenJohn said:
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...
Click to expand...

Hi, Thank you sooooooo much CJ_London my problem solved.
 
You must log in or register to reply here.

Similar threads

D
help needed tricky issue
2
Replies
28
Views
1,380
dtdukes86
D
N
Help new User. Calculation: Field a + field b = field c (and express in all field c records in table)
2
Replies
32
Views
2,091
NewUser76
N
LadyoftheForest
Subform or query help
Replies
15
Views
1,006
Pat Hartman
P
I
Help with query count
Replies
2
Views
319
ielamrani
I
S
Help with crossjoin, maketable/applen query, and then edit new table
2
Replies
27
Views
3,155
Sam_I_Am
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom