Calculate in and out time

  • Thread starter Thread starter jale
  • Start date Start date
J

jale

Hi

I have data from a time and attendance system that I want
to process. Each employee works on a job. When he starts a
job he clocks in, and when he finishes he clocks out. The
problem is that the in and out time is in the same field
with another field used to stipulate whether the time is
for in or out. How do I get access to calculate the time
between in and out.
My table is.:
EmpID Action DateTime
1851 IN 11.03.2004 09:00:00
1851 OUT 11.03.2004 09:20:00
1851 IN 11.03.2004 10:00:00
1851 OUT 11.03.2004 12:00:00
1851 IN 11.03.2004 13:30:00
1851 OUT 11.03.2004 18:30:00


Total time query is:
1851, 7hour and 20 minutes.

EmpID:Number
Action:Text
DateTime:General Date

I tried figure out DateDiff and and many type queries and couldn't do it.
Any help will be really appreciated.



thanks
 
I built a system like this years ago for Swipe cards to replace punch
clocks.
Luckily I was able to have a little influence on the table design.
Bottom line was I had 2 fields on 1 record - for In and Out.
The trick was making sure the Out got posted to the open record and then
next In got put on a new record.

My first reaction on looking at your data is to write a Sub procedure to
process the data instead of a query.
 
jale said:
Hi

I have data from a time and attendance system that I want
to process. Each employee works on a job. When he starts a
job he clocks in, and when he finishes he clocks out. The
problem is that the in and out time is in the same field
with another field used to stipulate whether the time is
for in or out. How do I get access to calculate the time
between in and out.
My table is.:
EmpID Action DateTime
1851 IN 11.03.2004 09:00:00
1851 OUT 11.03.2004 09:20:00
1851 IN 11.03.2004 10:00:00
1851 OUT 11.03.2004 12:00:00
1851 IN 11.03.2004 13:30:00
1851 OUT 11.03.2004 18:30:00


Total time query is:
1851, 7hour and 20 minutes.

EmpID:Number
Action:Text
DateTime:General Date

I tried figure out DateDiff and and many type queries and couldn't do it.
Any help will be really appreciated.
Click to expand...

CREATE TABLE MyTable_031304_1
(TimeID INTEGER
,EmpID INTEGER
,Action CHAR(3)
,[DateTime] DATE
,CONSTRAINT pk_MyTable_031304_1 PRIMARY KEY (TimeID)
)

NOTE: "DateTime" is an MS Access keyword, it's best not to use it for a
column name.
NOTE: TimeID is just the primary key I tossed in to have one in the example
table.

Sample Data
1 1851 IN 11/03.2004 09:00:00
2 1851 OUT 11/03.2004 09:20:00
3 1851 IN 11/03.2004 10:00:00
4 1851 OUT 11/03.2004 12:00:00
5 1851 IN 11/03.2004 13:30:00
6 1851 OUT 11/03.2004 18:30:00
7 1900 IN 11/03/2004 22:00:00
8 1900 OUT 11/04/2004 01:00:00


SELECT M1.EmpID
,FORMAT(M1.[DateTime] -
(SELECT M01.[DateTime]
FROM MyTable_031304_1 AS M01
WHERE M01.EmpID = M1.EmpID
AND M01.[DateTime] =
(SELECT MAX(M02.[DateTime])
FROM MyTable_031304_1 AS M02
WHERE M02.[DateTime] < M1.[DateTime]
AND M02.Action = "IN")), "hh:mm:ss") AS ShiftTime
FROM MyTable_031304_1 AS M1
WHERE M1.ACTION = "OUT"
 
Hi Chris2

Thank you so much for sharing your solution with me.It works.I changed all
your suggestion in my database.
My little problem is subquery is show all details.

Table2Query EmpID Shifttime
1851 00:20:00
1851 02:00:00
1851 05:00:00
1900 03:00:00
1900 01:00:00
..
How can I figure out Total hour....

1851 7 hours 20 min
1900 4 hours.

I tried new query from yours.

x:Format(Sum([Shifttime]);hh:nn:ss) is not worked.

And also tried on report not groupped shifttime.

Thanks for help




Chris2 said:
jale said:
Hi

I have data from a time and attendance system that I want
to process. Each employee works on a job. When he starts a
job he clocks in, and when he finishes he clocks out. The
problem is that the in and out time is in the same field
with another field used to stipulate whether the time is
for in or out. How do I get access to calculate the time
between in and out.
My table is.:
EmpID Action DateTime
1851 IN 11.03.2004 09:00:00
1851 OUT 11.03.2004 09:20:00
1851 IN 11.03.2004 10:00:00
1851 OUT 11.03.2004 12:00:00
1851 IN 11.03.2004 13:30:00
1851 OUT 11.03.2004 18:30:00


Total time query is:
1851, 7hour and 20 minutes.

EmpID:Number
Action:Text
DateTime:General Date

I tried figure out DateDiff and and many type queries and couldn't do it.
Any help will be really appreciated.
Click to expand...

CREATE TABLE MyTable_031304_1
(TimeID INTEGER
,EmpID INTEGER
,Action CHAR(3)
,[DateTime] DATE
,CONSTRAINT pk_MyTable_031304_1 PRIMARY KEY (TimeID)
)

NOTE: "DateTime" is an MS Access keyword, it's best not to use it for a
column name.
NOTE: TimeID is just the primary key I tossed in to have one in the example
table.

Sample Data
1 1851 IN 11/03.2004 09:00:00
2 1851 OUT 11/03.2004 09:20:00
3 1851 IN 11/03.2004 10:00:00
4 1851 OUT 11/03.2004 12:00:00
5 1851 IN 11/03.2004 13:30:00
6 1851 OUT 11/03.2004 18:30:00
7 1900 IN 11/03/2004 22:00:00
8 1900 OUT 11/04/2004 01:00:00


SELECT M1.EmpID
,FORMAT(M1.[DateTime] -
(SELECT M01.[DateTime]
FROM MyTable_031304_1 AS M01
WHERE M01.EmpID = M1.EmpID
AND M01.[DateTime] =
(SELECT MAX(M02.[DateTime])
FROM MyTable_031304_1 AS M02
WHERE M02.[DateTime] < M1.[DateTime]
AND M02.Action = "IN")), "hh:mm:ss") AS ShiftTime
FROM MyTable_031304_1 AS M1
WHERE M1.ACTION = "OUT"



thanks
Click to expand...
Click to expand...
 
jale said:
Hi Chris2

Thank you so much for sharing your solution with me.It works.I changed all
your suggestion in my database.
My little problem is subquery is show all details.

Table2Query EmpID Shifttime
1851 00:20:00
1851 02:00:00
1851 05:00:00
1900 03:00:00
1900 01:00:00
.
How can I figure out Total hour....

1851 7 hours 20 min
1900 4 hours.
Click to expand...

To display as shown immediately above:

DatePart("h", Table2.Shifttime) & " Hours " & DatePart("m",
Table2.Shifttime) & " Minutes"

To arrive at the summary of all Shifttimes:

I added two more rows of Sample Date to accomodate the second shift
segment for employee 1900.

9 1900 IN 11/04/2004 03:00:00
10 1900 OUT 11/04/2004 04:00:00

The problem is that in the last query, the output of FORMAT is a STRING
datatype (which we cannot use "SUM" on). We need it's output as a DATE
datatype, so:

SELECT M1.EmpID
,CDate(FORMAT(M1.[DateTime] -
(SELECT M01.[DateTime]
FROM MyTable_031304_1 AS M01
WHERE M01.EmpID = M1.EmpID
AND M01.[DateTime] =
(SELECT MAX(M02.[DateTime])
FROM MyTable_031304_1 AS M02
WHERE M02.[DateTime] < M1.[DateTime]
AND M02.Action = "IN")), "hh:mm:ss")) AS ShiftTime
FROM MyTable_031304_1 AS M1
WHERE M1.ACTION = "OUT"

Then:

SELECT Q1.EmpID
,FORMAT(SUM(Q1.ShiftTime), "hh:mm:ss")
FROM Find_MyTable_031304_1_ShiftTimes AS Q1
GROUP BY Q1.EmpID

The output is as you specified (7 hours 20 minutes for 1851, and 4 hours
for 1900). Apply the DatePart example above to put display it in "<h> hours
<m> minutes" format.


Sincerely,

Chris O.
I tried new query from yours.

x:Format(Sum([Shifttime]);hh:nn:ss) is not worked.
Click to expand...

^ should be ","

And also tried on report not groupped shifttime.

Thanks for help




Chris2 said:
jale said:
Hi

I have data from a time and attendance system that I want
to process. Each employee works on a job. When he starts a
job he clocks in, and when he finishes he clocks out. The
problem is that the in and out time is in the same field
with another field used to stipulate whether the time is
for in or out. How do I get access to calculate the time
between in and out.
My table is.:
EmpID Action DateTime
1851 IN 11.03.2004 09:00:00
1851 OUT 11.03.2004 09:20:00
1851 IN 11.03.2004 10:00:00
1851 OUT 11.03.2004 12:00:00
1851 IN 11.03.2004 13:30:00
1851 OUT 11.03.2004 18:30:00


Total time query is:
1851, 7hour and 20 minutes.

EmpID:Number
Action:Text
DateTime:General Date

I tried figure out DateDiff and and many type queries and couldn't do it.
Any help will be really appreciated.
Click to expand...

CREATE TABLE MyTable_031304_1
(TimeID INTEGER
,EmpID INTEGER
,Action CHAR(3)
,[DateTime] DATE
,CONSTRAINT pk_MyTable_031304_1 PRIMARY KEY (TimeID)
)

NOTE: "DateTime" is an MS Access keyword, it's best not to use it for a
column name.
NOTE: TimeID is just the primary key I tossed in to have one in the example
table.

Sample Data
1 1851 IN 11/03.2004 09:00:00
2 1851 OUT 11/03.2004 09:20:00
3 1851 IN 11/03.2004 10:00:00
4 1851 OUT 11/03.2004 12:00:00
5 1851 IN 11/03.2004 13:30:00
6 1851 OUT 11/03.2004 18:30:00
7 1900 IN 11/03/2004 22:00:00
8 1900 OUT 11/04/2004 01:00:00


SELECT M1.EmpID
,FORMAT(M1.[DateTime] -
(SELECT M01.[DateTime]
FROM MyTable_031304_1 AS M01
WHERE M01.EmpID = M1.EmpID
AND M01.[DateTime] =
(SELECT MAX(M02.[DateTime])
FROM MyTable_031304_1 AS M02
WHERE M02.[DateTime] < M1.[DateTime]
AND M02.Action = "IN")), "hh:mm:ss") AS ShiftTime
FROM MyTable_031304_1 AS M1
WHERE M1.ACTION = "OUT"



thanks
Click to expand...
Click to expand...
Click to expand...
 
You are great..Thanks thanks..It works perfectly...




Chris2 said:
jale said:
Hi Chris2

Thank you so much for sharing your solution with me.It works.I changed all
your suggestion in my database.
My little problem is subquery is show all details.

Table2Query EmpID Shifttime
1851 00:20:00
1851 02:00:00
1851 05:00:00
1900 03:00:00
1900 01:00:00
.
How can I figure out Total hour....

1851 7 hours 20 min
1900 4 hours.
Click to expand...

To display as shown immediately above:

DatePart("h", Table2.Shifttime) & " Hours " & DatePart("m",
Table2.Shifttime) & " Minutes"

To arrive at the summary of all Shifttimes:

I added two more rows of Sample Date to accomodate the second shift
segment for employee 1900.

9 1900 IN 11/04/2004 03:00:00
10 1900 OUT 11/04/2004 04:00:00

The problem is that in the last query, the output of FORMAT is a STRING
datatype (which we cannot use "SUM" on). We need it's output as a DATE
datatype, so:

SELECT M1.EmpID
,CDate(FORMAT(M1.[DateTime] -
(SELECT M01.[DateTime]
FROM MyTable_031304_1 AS M01
WHERE M01.EmpID = M1.EmpID
AND M01.[DateTime] =
(SELECT MAX(M02.[DateTime])
FROM MyTable_031304_1 AS M02
WHERE M02.[DateTime] < M1.[DateTime]
AND M02.Action = "IN")), "hh:mm:ss")) AS ShiftTime
FROM MyTable_031304_1 AS M1
WHERE M1.ACTION = "OUT"

Then:

SELECT Q1.EmpID
,FORMAT(SUM(Q1.ShiftTime), "hh:mm:ss")
FROM Find_MyTable_031304_1_ShiftTimes AS Q1
GROUP BY Q1.EmpID

The output is as you specified (7 hours 20 minutes for 1851, and 4 hours
for 1900). Apply the DatePart example above to put display it in "<h> hours
<m> minutes" format.


Sincerely,

Chris O.
I tried new query from yours.

x:Format(Sum([Shifttime]);hh:nn:ss) is not worked.
Click to expand...

^ should be ","

And also tried on report not groupped shifttime.

Thanks for help




Chris2 said:
Hi

I have data from a time and attendance system that I want
to process. Each employee works on a job. When he starts a
job he clocks in, and when he finishes he clocks out. The
problem is that the in and out time is in the same field
with another field used to stipulate whether the time is
for in or out. How do I get access to calculate the time
between in and out.
My table is.:
EmpID Action DateTime
1851 IN 11.03.2004 09:00:00
1851 OUT 11.03.2004 09:20:00
1851 IN 11.03.2004 10:00:00
1851 OUT 11.03.2004 12:00:00
1851 IN 11.03.2004 13:30:00
1851 OUT 11.03.2004 18:30:00


Total time query is:
1851, 7hour and 20 minutes.

EmpID:Number
Action:Text
DateTime:General Date

I tried figure out DateDiff and and many type queries and couldn't
Click to expand...
do
it.
Any help will be really appreciated.

CREATE TABLE MyTable_031304_1
(TimeID INTEGER
,EmpID INTEGER
,Action CHAR(3)
,[DateTime] DATE
,CONSTRAINT pk_MyTable_031304_1 PRIMARY KEY (TimeID)
)

NOTE: "DateTime" is an MS Access keyword, it's best not to use it for a
column name.
NOTE: TimeID is just the primary key I tossed in to have one in the example
table.

Sample Data
1 1851 IN 11/03.2004 09:00:00
2 1851 OUT 11/03.2004 09:20:00
3 1851 IN 11/03.2004 10:00:00
4 1851 OUT 11/03.2004 12:00:00
5 1851 IN 11/03.2004 13:30:00
6 1851 OUT 11/03.2004 18:30:00
7 1900 IN 11/03/2004 22:00:00
8 1900 OUT 11/04/2004 01:00:00


SELECT M1.EmpID
,FORMAT(M1.[DateTime] -
(SELECT M01.[DateTime]
FROM MyTable_031304_1 AS M01
WHERE M01.EmpID = M1.EmpID
AND M01.[DateTime] =
(SELECT MAX(M02.[DateTime])
FROM MyTable_031304_1 AS M02
WHERE M02.[DateTime] < M1.[DateTime]
AND M02.Action = "IN")), "hh:mm:ss") AS ShiftTime
FROM MyTable_031304_1 AS M1
WHERE M1.ACTION = "OUT"







thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top