Results 1 to 7 of 7
  1. #1
    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. #2
    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. #3
    Join Date
    May 2009
    Posts
    12
    Is the data in a single field, like a time stamp, or seperated into individual fields?

  4. #4
    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. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #6
    Join Date
    Aug 2009
    Posts
    5
    Thanks. I'll give it a try.

  7. #7
    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
  •