Determine # of Days a request was in a each status

[gopherking]

Ok, I am totally stumped on this one and am in need of some serious help!!

I am being fed data from another program which provides me with the Request #, the Status, and the Date that the Status was set. What I am trying to find out is how long the request sat in that status until it was changed to something else (or if it is currently still in that status). Unfortunately, they do not capture when the status is changed except for the Date of when the next status is set. Any ideas on how to manage this in a query? Attached is a sample of the data.

[alansidman]

There is nothing attached.

[gopherking]

Didn't notice that you can't upload Excel files ... should be a word doc attached with a table of data in it.

[alansidman]

Suggest you create a cross tab query by project with the different status as fields. The you can have each field (status) updated.

Take a look at this:

http://www.datapigtechnologies.com/f.../crosstab.html

If I am understanding you correctly.

Alan

[gopherking]

Alan,

Where that does work to display the date the project was changed to a new status, it doesn't allow for a calculation to determine how many days the project was in that status. One of the unfortunate things is that not all status' must be used and some of them could occur before or after others (wish there was a direct process flow).

However, in typing this I did think of a possible solution in using the current date to help identify the actual number of days spent in each status. I'm going to try something and then will post my update.

Sometimes it's just good to talk about it a bit for the gears to get running again to come up with a possible solution. Thanks!

[alansidman]

Good Luck and please post back with your solution for the benefit of others.

[gopherking]

Ok, so my original thought of using the Current Date to determine the number of days fell through ...

BUT!!!

I was able to come up with the following code to resolve my issue! Please note that this is my first iteration at this code and I will probably come back to it in the future to enhance it, but in the interim, I hope that this will help others that are having the same issue as me ...

There are two items of importance for the code below to work as expected:

1) Ensure that you have checked the "Microsoft DAO 3.6 Object Library" option in the References for your database

2) Your rec1 Table is sorted accordingly; mine is sorted by Request ID first and then by Date, both ascending, to ensure that all records for a specific Request ID are together and then sorted by the Date field chronologically to ensure the calculations work

(NOTE: Thanks to the export that I am receiving, the fields I have in my DB have spaces, so this code also shows you how to deal with Field Names with Spaces when referenced in a Recordset)

Private Sub Command0_Click()
    Dim db As Database
    Dim rec1 As DAO.Recordset
    Dim rec2 As DAO.Recordset
    Dim CurReqID, NextReqID, CurDate, NextDate, CurStatus, CurActBy, CurDays As String
    
    'Set DB and Recordsets
    Set db = CurrentDb
    'Rec1 is the initial Table that houses the current data; Sorted by Request ID and then by Date
    Set rec1 = db.OpenRecordset("Request_Status_History")
    'Rec2 is the table where the updated data will be stored including the # of Days a request remained in a
        'specific status.
    Set rec2 = db.OpenRecordset("Request_Status_History_w/Days")
    
    'Start Loop of initial Talble
    Do While Not rec1.EOF
        'Set variables of current record
        CurReqID = rec1![Request ID]
        CurDate = rec1!Date
        CurStatus = rec1!Status
        CurActBy = rec1![Action By]
Debug.Print CurReqID, CurDate, CurStatus, CurActBy
        
        'Goto the next record in the table
        rec1.MoveNext
        'Check for EOF
        If Not rec1.EOF Then
            'Set variables of Next Record
            NextReqID = rec1![Request ID]
            NextDate = rec1!Date
            Else
                'If EOF, set dummy variables
                NextReqID = "EOF"
                NextDate = "EOF"
        End If
        
Debug.Print NextReqID, NextDate
        'Check to see if Current Request ID and Next Request ID are equal
        If CurReqID = NextReqID Then
            'If they are equal, subtract Current Record Date (past date) from Next Record Date (future date)
            CurDays = NextDate - CurDate
            Else
                'If not, then this is the latest status for the Request ID; subtract from Current Date
                CurDays = Now() - CurDate
        End If
        
Debug.Print CurDays
        
        'Using the Current Record variables and the CurDate variable, populate a new entry into the update table
        With rec2
            .AddNew
            ![Request ID] = CurReqID
            !Status = CurStatus
            !Date = CurDate
            ![Action By] = CurActBy
            !Days = CurDays
            .Update
        End With
    Loop
    
    'Display a msg when completed
    MsgBox "All Records Updated to new table!", vbOKOnly
        
End Sub

Hope this helps anyone that comes across it!

Thanks to Alan for getting my hamster running in it's wheel again!