Create Query to determine if employee is currently punched out or

[Gahbeck]

I am trying to create a query that will check the current clocked in or out
status of employees and return a variable of out or in. The employees may
punch in and out 2 or 3 times per day. I have a table with the fields
Time_ID, Employee_Number, Department, Timestamp, In_Out, and Comments. The
Timestamp field is a set to now() and the employee selects In or Out
depending on what he or she is doing. I am trying to figure out how I can
look at the timestamps in the computer and figure out what his or her last
timestamp was... in or out. Would appreciate any suggestions.

Gary Hollenbeck
Production Manager
Western Cabinet Doors, Inc.

 

[Michel Walsh]

SELECT *
FROM tableName AS b
WHERE employeeID=1212
AND dateTimeStamp = (SELECT MAX(a.dateTimeStamp)
FROM tableName AS a
WHERE a.employeeID=b.employeeID)


where I assumed you are looking for employeeID 1212

and dateTimeStamp is the field name with the date and time of the punch
operations.




Vanderghast, Access MVP

 

[Gahbeck]

Michel,

Thank you for rapid reply. This works for giving me the last punch out for
a specified employeeID, but does not return the results of all employees last
punch whether they are in or out. I am sure that I was not clear enough in
my first post. My purpose is to double check an employee that forgot to
punch in or out, have a msg box inform the employee to go to the office
because he or she forgot to punch either in or out. I currently have a form
that the employee enters Time_in or Time_out in list box, Department, and
EmployeeID and a button to save the record and cycle to a blank form ready
for the next employee. I would like to prevent the employee from punching
in.. twice because they forgot to punch out or vice versa.. It may be easier
just to do this is as an expression in the afterupdate property. I apologize
for not being more clear.

Gary Hollenbeck
Production Manager
Western Cabinet Doors, Inc.

 

[Michel Walsh]

You can then use one of the four techniques presented in
http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP

 

[Gahbeck]

Michel,

Thank you for your assistance! I will take your advice.

Gary Hollenbeck