Difference between time in and time out... but

[MelB]

I need to create a sql query to find the difference between 'time in' and 'time out' of a building... but unfortunately both the times are stored in the same field. I can't change the database design because it is propritary. The backend database is SQL and the fields I am working with are...

Who
Where
When

and sample records are...
Joe Smith Main Office In 10/11/2004 08:00:00
Joe Smith Main Office Out 10/11/2004 17:00:00
Joe Smith Main Office In 10/12/2004 08:25:00
Joe Smith Main Office Out 10/12/2004 15:00:00


I figured out how to do this if I export the data to Excel, I can just write a formula to subtract the time in the previous records. But I can't figure out how to do it in sql. I would appreciate any help anyone can offer... this is probably easy for someone but I'm not having any luck.

Thanks in advance...

 

[Jon K]

You can build two queries: one for the In records, the other the Out records.

Then in a third query, link the two queries by Who and Date, subtract the InWhen from the OutWhen in a field, and format it as hh:nn:ss in the Field Properties sheet.


If some people may get in the building one day but get out the next day, in extracting the In records and Out records, you will need to use a subquery to rank the records as 1,2.3,... for each Who according to the When field so that you can link the two queries by Who and Rank in the third query.

And if some people may stay in the building for over 24 hours, you can't format the difference as hh:nn:ss in the Properties sheet. You will need to use the Format() function to format the expression [OutWhen]-[InWhen] as Days Hours Minutes Seconds or Hours Minutes Seconds. (See the examples in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=62733 )

.

 

[MelB]

OK... I think I understand what you are saying but I guess my example wasn't very accurate. In reality, a person might go in and out of a secure area 10 times a day. So I can't match up the person and the day because there will be multiple entries/exits per day.

I figured out how to this in Excel by putting the entries in order by the person and the time. If the entry is an 'out' the formula finds the difference between it and the record above which would be the 'in'. How can I write a formula in Access that refers to the previous records?

 

[cable]

I don't know of a way to do this using queries, I would say you would need to loop through records via code.

 

[EMP]

Following Jon K's comments, I was able to display in two queries the time (in the format of hh:nn:ss) in two queries.

You can run the queries "qryTimeInside" and "qryTotalDailyTimeInside" in the database to see if they are what you wanted.

 

[ggreg]

I can do it in One query

MelB,

Look at attachment and run and look at qrytotaltime........

You can get it done by using Min and Max
if you got any question let me know.
Greg

 

[DLB]

I can do it in One query

Look at attachment and run and look at qrytotaltime........

You can get it done by using Min and Max

I downloaded your database and ran the query. It returned these results:-

Name	   Workday     TimeIN		      TimeOut		     totalTime
John Smith 10/18/2004  18/10/2004 7:13:58 AM  [b]18/10/2004 3:16:50 PM[/b]  08:02:52
John Smith 10/19/2004  19/10/2004 6:03:58 AM  19/10/2004 3:05:19 PM  09:01:21

from these times in the table:-
18/10/2004 7:13:58 AM
18/10/2004 9:27:51 AM
18/10/2004 9:29:24 AM
18/10/2004 3:07:19 PM
18/10/2004 3:16:50 PM
19/10/2004 6:03:58 AM
19/10/2004 7:03:58 AM
19/10/2004 12:29:19 PM
19/10/2004 3:05:19 PM


If the first time is IN, the second time is OUT, etc.
Then 18/10/2004 3:16:50 PM should be TimeIN, not TimeOut.

And subtracting the Min time from the Max time would ignore the fact that John Smith actually went out of the building twice on 18/10/2004, etc.

 

[ggreg]

reply

In our case we are not worried about the in between times
we are just trying to get the first log and last logoff of the day and my test data was only set up to test for getting the lowest time and highest time of the day. I guess I did not looked at MELB problem as close as I should have
but maybe my query can be modified to keep it at one query.

Thanks DLB, for Pointing out the problems with using my query as is.

 

[Jamiee]

Hi i found this thread very usefull for a query i'm building.

My problem is however, i used the example of EMP (Time In and Out A2K.zip ), the final query takes about 30minutes to complete.

The first query shows the activities of a specific person:
10:01:12 Fred Login
10:03:10 Fred Loading packages
10:16:10 Fred Drive
10:17:53 Fred Unloading Packages
...
18:09:01 Fred Logout

I need to know the time between each record. For example: the time between Loading Packages and Drive is the total time needed for loading packages: 10:16:10 - 10:03:10 = 00:13:00 hours

I managed to get a Expr1 collumn which counts the rows returned and a copy of that query with the Expr2=Expr1 + 1. Then joining the two (Expr1=Expr2) in a new query. Now THIS query takes about 30min while the query to count the rows takes about 2 seconds...

Query1
Time Name expr1 Event
10:01:12 Fred 1 Login
10:03:10 Fred 2 Loading packages
10:16:10 Fred 3 Drive
10:17:53 Fred 4 Unloading Packages
...
18:09:01 Fred 54 Logout

Query2
expr2
10:01:12 Fred 2 Login
10:03:10 Fred 3 Loading packages
10:16:10 Fred 4 Drive
10:17:53 Fred 5 Unloading Packages
...
18:09:01 Fred 55 Logout

The result should be:
Query3
Time2 Time1 Timedif Name etc.etc.etc.
10:03:10 10:01:12 00:01:58 Fred 2 Login

Now Query3 takes about 30min

Any help is appreciated a lot!

 

[Jamiee]

I found the solution myself:

Query1 takes 2 seconds > MAKE-TABLE Query
Query2 takes 2 seconds > MAKE-TABLE Query

Query3 (which combines Q1 and Q2) takes 4 seconds total

I'm happy!!!

This is the code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1", acViewNormal, acEdit
DoCmd.OpenQuery "Query2", acViewNormal, acEdit
DoCmd.OpenQuery "Query3", acViewNormal, acEdit

 

[sethuc]

Hi

Thanks for the code.
1) I have around 30,00 records for a month for different users and it takes 10-15 mins to run the macro. How should I make it run fast (I used code from Time In and Out A2K)

2) Since the logout is around the clock, logout out might run to next day also. I thought of finding the difference hours between each logout and next login and if difference is more than 5 hrs then the next login should be considered as next day or it should be considered as same day.

emp id Login Logout day
--------------------------------------------------------------
1 17-08-09 23:20 17-08-09 23:50 1
1 18-08-09 00:20 18-08-09 03:30 1
1 18-08-09 17:30 18-08-09 18:10 2

any SQL query to compare the 1st logout and immediate next login or if you think a different logic would work easily, I'm happy

Thanks in advance for your help.