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