M A

asked on 1/27/2020

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

Last Comment

Gustav Brock

8/22/2022 - Mon

OMC2000

1/27/2020

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

1/27/2020

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

1/27/2020

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

1/27/2020

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

1/28/2020

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

1/28/2020

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

1/28/2020

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

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

1/28/2020

ASKER

@Gustav Brock

I need date and time together.

Gustav Brock

1/28/2020

Then remove TimeValue from the two expressions.