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:
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.
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(?).
SELECT txn_id, store_id, DATEDIFF("s",MAX_TIME, MIN_TIME)
FROM (SELECT txn_id
, MAX(txn_tm) as MAX_TIME
, MIN(txn_tm) as MIN_TIME
GROUP BY txn_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.