Link to home
Start Free TrialLog in
Avatar of M A
M AFlag for United States of America

asked on

Punchin and punchout time access database

Get min and Max value.
I have a database for attendance. Below is the only two fields in the database.
EmpID     PUNCHDATETIME.  
1             27/1/2020 10:00:00
1             27/1/2020 13:00:00
1.            27/1/2020  17:00:00
2             27/1/2020 10:12:00
2             27/1/2020 13:10:00
2.            27/1/2020  17:15:00
3.            27/1/2020 10:08:00
3.            27/1/2020 13:00:00
3.           27/1/2020  17:06:00

I want to see take out min time as punch in and import to HR system
And take Max time as punch out and import to HR system.
How to create a query or update to get the data.

Thanks
Avatar of OMC2000
OMC2000
Flag of Russian Federation image

As far as I understand you the query should be like the following

select EmpID     , min(PUNCHDATETIME),   max(PUNCHDATETIME) from your_table group by EmpID  order by EmpID  

Open in new window

Avatar of Daniel Pineault
Daniel Pineault

For the Min you can try something like:
SELECT EmpId, Min(PUNCHDATETIME) AS MinOfPUNCHDATETIME
FROM YourTableName
GROUP BY EmpId, Format([PUNCHDATETIME],"dd\/mm\/yyyy");

Open in new window


For the Max you can try something like:
SELECT EmpId, Max(PUNCHDATETIME) AS MaxOfPUNCHDATETIME
FROM YourTableName
GROUP BY EmpId, Format([PUNCHDATETIME],"dd\/mm\/yyyy");

Open in new window


You could also use DateValue([PUNCHDATETIME]) for the GROUP BY instead of Format([PUNCHDATETIME],"dd\/mm\/yyyy")

Or, for a full picture
SELECT t1.EmpId, t1.PunchIn, t2.PunchOut
FROM (
SELECT EmpId, DateValue([PUNCHDATETIME]) AS PUNCHDATE, Min(PUNCHDATETIME) AS PunchIn
FROM YourTableName
GROUP BY EmpId, DateValue([PUNCHDATETIME])
) AS t1
LEFT JOIN (
SELECT EmpId, DateValue([PUNCHDATETIME]) AS PUNCHDATE, Max(PUNCHDATETIME) AS PunchOut
FROM YourTableName
GROUP BY EmpId, DateValue([PUNCHDATETIME])
) AS t2 ON t1.EmpId=t2.EmpId AND t1.PUNCHDATE=t2.PUNCHDATE

Open in new window

If you have a shift that crosses the midnight fence, you'll need to consider a punchin date/time on one day and a punchout date/time on the next day.
Also, what is the deal with 3 date/time punches for an employee?  How do you determine what's a punchin and what's a punchout when you have more than 2 punches?  And what do you do with the other punches?
As Mark mentioned we are missing here the "I/O" some field that hold a value like 1/-1 or 0/1 that marks the entrance - exit.
Avatar of M A

ASKER

Good question.

--> what's a punchout when you have more than 2 punches?  And what do you do with the other punches?

The first punch time will be punch in and the last punch time will be Punch out.

This is what I need.


Attached database.

att2000-1.mdb

Then its
SELECT Table1.EmpID, Min(Table1.PUNCHDATETIME) AS Entry, Max(Table1.PUNCHDATETIME) AS Exit
FROM Table1
GROUP BY Table1.EmpID;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M A

ASKER

@Gustav Brock

I need date and time together.

Then remove TimeValue from the two expressions.