# Thread: Calculate time btwn 2 rows

1. Registered User
Join Date
Aug 2009
Posts
5

## Unanswered: Calculate time btwn 2 rows

Hello everyone,

I am not completely unfamiliar with Access, but am no pro either. Can someone help?

I have a data base that gives me every transaction done on a cash register. I know how to run a query and search for specific transactions in a specific store, on specific dates. However, what I want to figure out is how to calculate the time between the first and last transaction on a given day. My data looks as follow:

txn_id store_id txn_dte txn_tm
6121097001152 970 1/4/2004 2004-01-04 09:58:00
6121097002825 970 1/4/2004 2004-01-04 12:54:00
6121097004283 970 1/4/2004 2004-01-04 15:05:00
6121097004337 970 1/4/2004 2004-01-04 15:08:00
6121097004366 970 1/4/2004 2004-01-04 15:10:00
6121097004353 970 1/4/2004 2004-01-04 15:10:00
6121097004421 970 1/4/2004 2004-01-04 15:15:00
6121097004448 970 1/4/2004 2004-01-04 15:17:00
6121097004458 970 1/4/2004 2004-01-04 15:18:00
6121097004481 970 1/4/2004 2004-01-04 15:21:00
6121097004505 970 1/4/2004 2004-01-04 15:22:00
6121097004524 970 1/4/2004 2004-01-04 15:24:00
6121097004911 970 1/4/2004 2004-01-04 15:54:00
6121097004927 970 1/4/2004 2004-01-04 15:55:00
6121097004969 970 1/4/2004 2004-01-04 15:59:00
6121097004975 970 1/4/2004 2004-01-04 16:00:00

I will have other issues I am sure, but for now I want to get past this one. Can anyone help? Any and all help is GREATLY appreciated.

2. Registered User
Join Date
Aug 2009
Posts
5
Anyone? All I need is to find the difference in time between first row and last row on a given date.

3. Registered User
Join Date
May 2009
Posts
12
Is the data in a single field, like a time stamp, or seperated into individual fields?

4. Registered User
Join Date
Aug 2009
Posts
5
The data is just as it appears above. Every single transaction on the register is in a separate row. As you can imagine there are a couple thousand entries per month. I just want to go in a figure out how much time a person spent on the register each day.

5. Registered User
Join Date
May 2009
Posts
509
astrom33, I think what you need is something like what is below. I am not an Access syntax expert so what I have may not work as it is but it might get you close.

Using DATEFIFF, you can get the difference between these two values (probably in seconds). Then you would have to convert the total seconds into Hour, Minute and Seconds (assuming the range is less than 24 hours. If it is, you might need Days also).

You might also need the store_id and/or txn_id to get the MAX/MIN values for the correct person(?).
Code:
```SELECT txn_id, store_id, DATEDIFF("s",MAX_TIME, MIN_TIME)
FROM (SELECT txn_id
, store_id
, MAX(txn_tm) as MAX_TIME
, MIN(txn_tm) as MIN_TIME
FROM table-name
GROUP BY txn_id
, store_id
) as A```
If this works, it will give the the number of seconds between the minimum and maximum date/time values each distinct TXN_ID, STORE_ID value.

Once you have seconds you can divide by 60 for minutes and 3600 for hours.

6. Registered User
Join Date
Aug 2009
Posts
5
Thanks. I'll give it a try.

7. Registered User
Join Date
Aug 2009
Posts
5
Quick question. Does the field Txn date come in to play at all? There are numerous transactions each day and I want to know the time spent each day at the cash register.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•