We help IT Professionals succeed at work.

Punchin and punchout time access database

MAS
MAS asked
on
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
Comment
Watch Question

View Solution Only

OMC2000

Commented:
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 PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
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 EdwardsChief Technology Officer

Commented:
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?
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
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.
MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Author

Commented:

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 TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
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

CIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:

This will do:

Select 
    EmpID, 
    DateValue([PUNCHDATETIME]) As [Date], 
    TimeValue(Min([PUNCHDATETIME])) As PunchIn, 
    TimeValue(Max([PUNCHDATETIME])) As PunchOut 
From 
    Punch 
Group By 
    EmpID, 
    DateValue([PUNCHDATETIME]);

MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Author

Commented:

@Gustav Brock

I need date and time together.

Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

Then remove TimeValue from the two expressions.