Punch time query to calculate total work time

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I have a table containing two fields; Employee ID and Time stamp, respectively.

Let's say the table contains the following values:

Emp ID Time Stamp
------- ------------
1001 1/1/2010 09:00
1001 1/1/2010 12:00
1001 1/1/2010 13:30
1001 1/1/2010 17:00

The data reflects that employee #1001 has clocked-in at 09:00 am and
clocked-out at 12:00pm. The employee has clocked-in again at 1:30 pm and
finally clocked out at 5:00 pm.

Since the data was generated using Employee ID card, there is no separate
time-in and time out field.

How can I query the table so that I can have something similar to the
following results:

Emp ID Time-in Time-out Total
Time Worked
------- ----------------- -----------------
--------------------
1001 1/1/2010 09:00 1/1/2010 12:00 03:00
1001 1/1/2010 13:30 1/1/2010 17:00 03:30
------------------------------------------------------------------------
Total
06:30

I am somewhat new to Access and Thank you in advance for your help!

Keith
 
I would first create a query that ranks/numbers each time entry by employee.
This would assume the earliest entry is a clock in:

SELECT tblTimeClock.EmpID, tblTimeClock.TimeStamp,
(SELECT Count(*) FROM tblTimeClock t2
WHERE t2.EmpID =tblTimeClock.EmpID and t2.TimeStamp
<=tblTimeClock.TimeStamp) AS TheOrder
FROM tblTimeClock
ORDER BY tblTimeClock.EmpID, tblTimeClock.TimeStamp;

All of the odd numbered records should now be the clock in. Create another
query that filters for the clock in (odd) records and returns the next latest
time for the same employee:

SELECT qrnkTimeClock.EmpID, qrnkTimeClock.TimeStamp AS TimeIn, (SELECT TOP 1
TimeStamp FROM tblTimeClock WHERE tblTimeClock.EmpID = qrnkTimeClock.EmpID
and tblTimeClock.TimeStamp > qrnkTimeClock.TimeStamp ORDER BY TimeStamp) AS
TimeOut
FROM qrnkTimeClock
WHERE ((([TheOrder] Mod 2)=1));
 
Thanks for the help. Your suggestion worked great! Can you explain t2? is
t2 being used as a variable?

Duane Hookom said:
I would first create a query that ranks/numbers each time entry by employee.
This would assume the earliest entry is a clock in:

SELECT tblTimeClock.EmpID, tblTimeClock.TimeStamp,
(SELECT Count(*) FROM tblTimeClock t2
WHERE t2.EmpID =tblTimeClock.EmpID and t2.TimeStamp
<=tblTimeClock.TimeStamp) AS TheOrder
FROM tblTimeClock
ORDER BY tblTimeClock.EmpID, tblTimeClock.TimeStamp;

All of the odd numbered records should now be the clock in. Create another
query that filters for the clock in (odd) records and returns the next latest
time for the same employee:

SELECT qrnkTimeClock.EmpID, qrnkTimeClock.TimeStamp AS TimeIn, (SELECT TOP 1
TimeStamp FROM tblTimeClock WHERE tblTimeClock.EmpID = qrnkTimeClock.EmpID
and tblTimeClock.TimeStamp > qrnkTimeClock.TimeStamp ORDER BY TimeStamp) AS
TimeOut
FROM qrnkTimeClock
WHERE ((([TheOrder] Mod 2)=1));
--
Duane Hookom
Microsoft Access MVP


Keith said:
I have a table containing two fields; Employee ID and Time stamp, respectively.

Let's say the table contains the following values:

Emp ID Time Stamp
------- ------------
1001 1/1/2010 09:00
1001 1/1/2010 12:00
1001 1/1/2010 13:30
1001 1/1/2010 17:00

The data reflects that employee #1001 has clocked-in at 09:00 am and
clocked-out at 12:00pm. The employee has clocked-in again at 1:30 pm and
finally clocked out at 5:00 pm.

Since the data was generated using Employee ID card, there is no separate
time-in and time out field.

How can I query the table so that I can have something similar to the
following results:

Emp ID Time-in Time-out Total
Time Worked
------- ----------------- -----------------
--------------------
1001 1/1/2010 09:00 1/1/2010 12:00 03:00
1001 1/1/2010 13:30 1/1/2010 17:00 03:30
------------------------------------------------------------------------
Total
06:30

I am somewhat new to Access and Thank you in advance for your help!

Keith
Click to expand...
Click to expand...
 
t2 is a variable/alias that references tblTimeClock.

--
Duane Hookom
Microsoft Access MVP


Keith said:
Thanks for the help. Your suggestion worked great! Can you explain t2? is
t2 being used as a variable?

Duane Hookom said:
I would first create a query that ranks/numbers each time entry by employee.
This would assume the earliest entry is a clock in:

SELECT tblTimeClock.EmpID, tblTimeClock.TimeStamp,
(SELECT Count(*) FROM tblTimeClock t2
WHERE t2.EmpID =tblTimeClock.EmpID and t2.TimeStamp
<=tblTimeClock.TimeStamp) AS TheOrder
FROM tblTimeClock
ORDER BY tblTimeClock.EmpID, tblTimeClock.TimeStamp;

All of the odd numbered records should now be the clock in. Create another
query that filters for the clock in (odd) records and returns the next latest
time for the same employee:

SELECT qrnkTimeClock.EmpID, qrnkTimeClock.TimeStamp AS TimeIn, (SELECT TOP 1
TimeStamp FROM tblTimeClock WHERE tblTimeClock.EmpID = qrnkTimeClock.EmpID
and tblTimeClock.TimeStamp > qrnkTimeClock.TimeStamp ORDER BY TimeStamp) AS
TimeOut
FROM qrnkTimeClock
WHERE ((([TheOrder] Mod 2)=1));
--
Duane Hookom
Microsoft Access MVP


Keith said:
I have a table containing two fields; Employee ID and Time stamp, respectively.

Let's say the table contains the following values:

Emp ID Time Stamp
------- ------------
1001 1/1/2010 09:00
1001 1/1/2010 12:00
1001 1/1/2010 13:30
1001 1/1/2010 17:00

The data reflects that employee #1001 has clocked-in at 09:00 am and
clocked-out at 12:00pm. The employee has clocked-in again at 1:30 pm and
finally clocked out at 5:00 pm.

Since the data was generated using Employee ID card, there is no separate
time-in and time out field.

How can I query the table so that I can have something similar to the
following results:

Emp ID Time-in Time-out Total
Time Worked
------- ----------------- -----------------
--------------------
1001 1/1/2010 09:00 1/1/2010 12:00 03:00
1001 1/1/2010 13:30 1/1/2010 17:00 03:30
------------------------------------------------------------------------
Total
06:30

I am somewhat new to Access and Thank you in advance for your help!

Keith
Click to expand...
Click to expand...
Click to expand...
 
I understand perfectly. I was just wonder if there is a way to combine the
two queries into one query. Any thoughts?

Duane Hookom said:
t2 is a variable/alias that references tblTimeClock.

--
Duane Hookom
Microsoft Access MVP


Keith said:
Thanks for the help. Your suggestion worked great! Can you explain t2? is
t2 being used as a variable?

Duane Hookom said:
I would first create a query that ranks/numbers each time entry by employee.
This would assume the earliest entry is a clock in:

SELECT tblTimeClock.EmpID, tblTimeClock.TimeStamp,
(SELECT Count(*) FROM tblTimeClock t2
WHERE t2.EmpID =tblTimeClock.EmpID and t2.TimeStamp
<=tblTimeClock.TimeStamp) AS TheOrder
FROM tblTimeClock
ORDER BY tblTimeClock.EmpID, tblTimeClock.TimeStamp;

All of the odd numbered records should now be the clock in. Create another
query that filters for the clock in (odd) records and returns the next latest
time for the same employee:

SELECT qrnkTimeClock.EmpID, qrnkTimeClock.TimeStamp AS TimeIn, (SELECT TOP 1
TimeStamp FROM tblTimeClock WHERE tblTimeClock.EmpID = qrnkTimeClock.EmpID
and tblTimeClock.TimeStamp > qrnkTimeClock.TimeStamp ORDER BY TimeStamp) AS
TimeOut
FROM qrnkTimeClock
WHERE ((([TheOrder] Mod 2)=1));
--
Duane Hookom
Microsoft Access MVP


:

I have a table containing two fields; Employee ID and Time stamp, respectively.

Let's say the table contains the following values:

Emp ID Time Stamp
------- ------------
1001 1/1/2010 09:00
1001 1/1/2010 12:00
1001 1/1/2010 13:30
1001 1/1/2010 17:00

The data reflects that employee #1001 has clocked-in at 09:00 am and
clocked-out at 12:00pm. The employee has clocked-in again at 1:30 pm and
finally clocked out at 5:00 pm.

Since the data was generated using Employee ID card, there is no separate
time-in and time out field.

How can I query the table so that I can have something similar to the
following results:

Emp ID Time-in Time-out Total
Time Worked
------- ----------------- -----------------
--------------------
1001 1/1/2010 09:00 1/1/2010 12:00 03:00
1001 1/1/2010 13:30 1/1/2010 17:00 03:30
------------------------------------------------------------------------
Total
06:30

I am somewhat new to Access and Thank you in advance for your help!

Keith
Click to expand...
Click to expand...
Click to expand...
 
There probably is a way but it would be a little more difficult to
understand. To be honest, I won't take the time to play with it. I already
created your sample data, added more records than you had, built, and tested
to get what you asked for.


--
Duane Hookom
MS Access MVP


Keith said:
I understand perfectly. I was just wonder if there is a way to combine the
two queries into one query. Any thoughts?

Duane Hookom said:
t2 is a variable/alias that references tblTimeClock.

--
Duane Hookom
Microsoft Access MVP


Keith said:
Thanks for the help. Your suggestion worked great! Can you explain
t2? is
t2 being used as a variable?

:

I would first create a query that ranks/numbers each time entry by
employee.
This would assume the earliest entry is a clock in:

SELECT tblTimeClock.EmpID, tblTimeClock.TimeStamp,
(SELECT Count(*) FROM tblTimeClock t2
WHERE t2.EmpID =tblTimeClock.EmpID and t2.TimeStamp
<=tblTimeClock.TimeStamp) AS TheOrder
FROM tblTimeClock
ORDER BY tblTimeClock.EmpID, tblTimeClock.TimeStamp;

All of the odd numbered records should now be the clock in. Create
another
query that filters for the clock in (odd) records and returns the
next latest
time for the same employee:

SELECT qrnkTimeClock.EmpID, qrnkTimeClock.TimeStamp AS TimeIn,
(SELECT TOP 1
TimeStamp FROM tblTimeClock WHERE tblTimeClock.EmpID =
qrnkTimeClock.EmpID
and tblTimeClock.TimeStamp > qrnkTimeClock.TimeStamp ORDER BY
TimeStamp) AS
TimeOut
FROM qrnkTimeClock
WHERE ((([TheOrder] Mod 2)=1));
--
Duane Hookom
Microsoft Access MVP


:

I have a table containing two fields; Employee ID and Time stamp,
respectively.

Let's say the table contains the following values:

Emp ID Time Stamp
------- ------------
1001 1/1/2010 09:00
1001 1/1/2010 12:00
1001 1/1/2010 13:30
1001 1/1/2010 17:00

The data reflects that employee #1001 has clocked-in at 09:00 am
and
clocked-out at 12:00pm. The employee has clocked-in again at 1:30
pm and
finally clocked out at 5:00 pm.

Since the data was generated using Employee ID card, there is no
separate
time-in and time out field.

How can I query the table so that I can have something similar to
the
following results:

Emp ID Time-in Time-out
Total
Time Worked
------- ----------------- -----------------
--------------------
1001 1/1/2010 09:00 1/1/2010 12:00 03:00
1001 1/1/2010 13:30 1/1/2010 17:00 03:30
------------------------------------------------------------------------
Total
06:30

I am somewhat new to Access and Thank you in advance for your help!

Keith
Click to expand...
Click to expand...
Click to expand...
 
Back
Top