Results 1 to 2 of 2
  1. #1
    iaiabeans is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    1

    Complex Query on Transactional History Table


    Please help! I have been at this one for a few days and can't come up with a solution. I do not use SQL, but rather rely on the design tool in MS Access. However, I am open to anything at this point. I am working with a table that stores all of the history of electronic transactions that occur by unique identifier. Transactions are classififed by unique identifiers, new status codes they are entering, the old code from which they are exiting, and the time stamp for the new code they are entering. There are two status codes, 67 and 72, that I am trying to capture the duration from when they entered that code from when they leave. For example, if a transaction enters a 67 on 1/1/2016, and then leaves it on 1/15/2016, I want to capture the time span it took from point A to B, 14 days. The problem that I have is that a record may go in and out of these status codes multiple times and when this happens, I don't know how to tell Accesss to establish some sort of order so I can align the dates accrodingly. Please review a record I pulled from the table shich shows the field names and let me know what I can do. This is a key indicator that I wish to capture without having to export it to excel and do a lot of manipulation. Additionally, supporting explaination would be greatly appreciated as well. Thanks in advance!

    GUID_ID GROUP_TYPE OLD_STATUS NEW_STATUS UPDATED_USER UPDATED_DT
    123ABC TEXT 0 TEXT 12/4/2015 16:12
    123ABC TEXT 15 67 TEXT 12/7/2015 16:09
    123ABC TEXT 67 0 TEXT 12/8/2015 15:27
    123ABC TEXT 0 67 TEXT 12/8/2015 16:11
    123ABC TEXT 67 0 TEXT 12/9/2015 14:03
    123ABC TEXT 0 14 TEXT 12/9/2015 14:49
    123ABC TEXT 14 16 TEXT 12/9/2015 14:49
    123ABC TEXT 19 67 TEXT 12/11/2015 17:02
    123ABC TEXT 19 67 TEXT 12/14/2015 16:22
    123ABC TEXT 0 15 TEXT 12/15/2015 13:54
    123ABC TEXT 67 19 TEXT 12/15/2015 13:54
    123ABC TEXT 16 19 TEXT 12/15/2015 13:54
    123ABC TEXT 19 19 TEXT 12/15/2015 13:54
    123ABC TEXT 15 15 TEXT 12/15/2015 13:54
    123ABC TEXT 19 19 TEXT 12/15/2015 13:54
    123ABC TEXT 19 19 TEXT 12/15/2015 13:54
    123ABC TEXT 19 19 TEXT 12/15/2015 13:54
    123ABC TEXT 67 19 TEXT 12/15/2015 13:54
    123ABC TEXT 19 18 TEXT 12/15/2015 14:30
    123ABC TEXT 18 49 TEXT 12/15/2015 14:30
    123ABC TEXT 49 49 TEXT 12/15/2015 16:06
    123ABC TEXT 49 49 TEXT 12/16/2015 11:09
    123ABC TEXT 49 49 TEXT 12/16/2015 16:09
    123ABC TEXT 49 49 TEXT 12/17/2015 11:13
    123ABC TEXT 49 67 TEXT 12/17/2015 16:08
    123ABC TEXT 67 69 TEXT 12/28/2015 15:09
    123ABC TEXT 69 71 TEXT 12/28/2015 16:07

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you're still working on this if this is your original data set what do you want your resulting data to look like?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-01-2015, 03:03 PM
  2. Replies: 2
    Last Post: 07-22-2014, 09:30 AM
  3. complex (?) query for a huge table
    By jetmcquack in forum Queries
    Replies: 1
    Last Post: 05-16-2013, 04:44 PM
  4. History Table
    By Opid in forum Access
    Replies: 2
    Last Post: 05-07-2012, 10:50 AM
  5. Replies: 2
    Last Post: 04-05-2012, 09:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums