Avatar of M A
M A
Flag 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
DatabasesMicrosoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
OMC2000

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

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

Mark Edwards

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
John Tsioumpris

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

John Tsioumpris

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
M A

ASKER

@Gustav Brock

I need date and time together.

Gustav Brock

Then remove TimeValue from the two expressions.