Guidance needed: Difficult Data Selection 1

[DajTwo]

Note: VBA level is pretty low (3 day course)

I have a table with: [App], [Status], [Reason], [Time].

There is an unlimited number of App. There are 11 [Status] (duplicate are possible) and a limited number of [Reason] for each of the Status (duplicates are possible).

The Time field is a time stamp of when the Status/Reason fields were captured. The table is sorted by [App] then [Time]

I have the VBA code to calculate the Time Difference so that is not an issue.

App Status Reason Time Ex 1 Ex 2 Ex 3
98128 INCOMPLETE 7/21/08 1:18 PM 0.00
98128 COMPLETE 7/21/08 1:18 PM 0.44
98128 REVIEW 7/21/08 2:02 PM 0.00
98128 COMPLETE 7/21/08 2:02 PM 1.41 1.41
98128 REVIEW 7/21/08 3:43 PM 0.31 1.11
98128 PENDING ASSET 7/21/08 4:14 PM 0.40
98128 APPROVED 7/21/08 4:54 PM N/A
98129 INCOMPLETE 7/21/08 1:25 PM 0.00
98129 COMPLETE 7/21/08 1:25 PM 0.42
98129 REVIEW 7/21/08 2:07 PM 0.00 1.45
98129 COMPLETE 7/21/08 2:07 PM 1.37 1.37
98129 REVIEW 7/21/08 3:44 PM 0.08
98129 RESET TO NOT DONE 7/21/08 3:52 PM 0.00
98129 APPROVED 7/21/08 3:52 PM N/A
98131 INCOMPLETE 7/21/08 2:00 PM 0.00
98131 COMPLETE 7/21/08 2:00 PM 0.43
98131 REVIEW 7/21/08 2:43 PM 0.00 3.52
98131 COMPLETE 7/21/08 2:43 PM 3.02 3.02
98131 REVIEW REVIEW 7/22/08 9:15 AM 0.01
98131 DECISION DEPT HEAD 7/22/08 9:16 AM 0.49
98131 APPROVED 7/22/08 10:05 AM N/A

As examples, what I want to do is for each [App], calculate the [Time] difference between

Ex 1: Calculate for each [App] the time difference between the current record and the next record (for the same [App])

Ex 2: The last “COMPLETE” [Status] and a first “REVIEW” [Status] for each [App]

Ex 3: The first “Review” [Status] and the last “Approved” [Status for each [App]

And store the results in a table

In essence, to be able to select specific status or reason and do the time difference. I think I will need a “next i” to separate the [app] but after that I am lost..

Can someone point me into the right direction?

Much appreciated.

 

[kjv1611]

So, the data examples are in your "main" table, and what you are wanting to create is some sort of summary or history table as well?

Maybe you could do something like this:
Ex 1: Calculate for each [App] the time difference between the current record and the next record (for the same [App])
First, I'd suggest adding in some sort of RecordID field into your "main" table there. That will help with joins and sorting the records by date, assuming the more recent date is always added last. If you don't want to program the calculation of that field, you can just use an "auto number" format, just something to fill it in.
If you've already got the data there, then you can "fix" the existing data this way:
[ol][li]Copy existing table, and past the structure only of that table.[/li]
[li]Open the new table in design view, and add the Autonumber field.[/li]
[li]Create an Append Query to append the data from your old "main" table to the new "main" table:
[ol][li]Create a new query in design view.[/li]
[li]Double-click or "Add" your "main" table to the query, then click "close" in the "show table" prompt.[/li]
[li]Double-click the Asterisk (*) in your table in the query window, that will add it to the queried fields (* = all fields).[/li]
[li]On the Access menu bar, select "Query" - "Append Query"[/li]
[li]In the next prompt, select your new "main" table, which was a copy of the original plus the auto number field.[/li]
[li]Click the Red Exclamation (!) Mark on the Access toolbar[/li]
[li]When asked if you are sure you want to append the records, click "yes"[/li]
[li]Close your query - you won't need to save it.[/li]
[li]Your new table should be populated. Verify it is correct, then delete the old one, and rename the new one to match the old table's original name.[/li]
[/ol][/li][/ol]

I was going to continue on in the same post, but I think I'll leave it there, and then post some additional when I've got it ready.. the code (I think) could be done a couple of different ways...

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

One other note as well.. I'd stick something like a DateDiff field in your "main" table, and set it to a numeric format. It doesn't matter if before or after the query at this point....

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

One other thought - almost forgot. When you set up the "Date Diff" field in your table, make sure to change it's "default value" value to just a blank field - if it puts "0" in it, just delete the "0".

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I'll have to get back to you on the code for the first one... I was first thinking of creating 2 recordsets for looping through your "main" table, but I'm hoping theres a more efficient way. Maybe someone else will be able to get something in here in the mean time??

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

More thinking here:

Ex 1: Calculate for each [App] the time difference between the current record and the next record (for the same [App])
If you put an ID in there, and the records are always entered in such a manner that the "first" date is first, and the "last" date is last, in other words if this date is based off when the records are entered, then that may solve your issue. You wouldn't need to keep a record in a field of the difference between the two, but rather you could just query that when needed for any reporting.

Of course, you can still do fix it to the table. You could do one run of updating the existing records, and then have a smaller routine for updating any new records.

Ex 2: The last "COMPLETE" [Status] and a first "REVIEW" [Status] for each [App]
Assuming you'd have a History table or whatever, you could do it like this:
Table structure:
App (the app number would be the ID), and you'd probably want it to be the primary key here, not allowing duplicates, since you should only have one record per App ID, this way, I would think.

Each of the statuses layed out in fields... Complete, Review, etc, and then you could do one of two things.
1. Only list "Last complete", "first review" or if not too many columns required, you could have a first and last for each.

That can also be done with a query - query builder or VBA SQL.

However, this also may be able to be taken care of simply by having a query off of your "main" table like with the DateDiff one above.


Ex 3: The first "Review" [Status] and the last "Approved" [Status for each [App]

This would be taken care of with #2.

--

"If to err is human, then I must be some kind of human!" -Me

 

[DajTwo]

Sorry for the no reply but I am stuck in a day long meeting.. I will take this home tonight and reply tomorrow..

Great thanks ..

 

[DajTwo]

I agree with # 2 and # 1, I can do this by qry's .. Thanks

How about #1, How can I Calculate for each [App] the time difference between the current record and the next record (for the same [App])?

Again, I think it is a question of

For i = ?
Next i

but I dont know enough vba to formulate this yet..

 

[kjv1611]

How can I Calculate for each [App] the time difference between the current record and the next record (for the same [App])?

If you choose to use a query or VBA, you can use the DATEDIFF function. Try searching the helpfiles on that, and you should find what you need.
DateDiff («interval», «date1», «date2», «firstweekday», «firstweek»)

The only thing is, I'm not 100% sure exactly what it will return, as far as whether that is what you really want. You can return the number of seconds, which I would assume you want, like this:

DateDiff("s", Date1, Date2)

But there is lots of info out there, with lots of examples for its usage.

If you don't want to try that, you can just try strait subtraction:
Date1 - Date2...

You do it different ways in a query versus in VBA.

For instance, in VBA, you could create 2 DAO Recordsets for the same table, and compare that way.. possibly like this:

Private Sub GetDateDiff()
On Error GoTo ErrHandle
  Dim db as DAO.Database
  Dim rsOut as DAO.Recordset ' Outer loop - one you'll update
  Dim rsIn as DAO.Recordset ' Inner loop - one you'll check against
  Do While Not rsOut.EOF
  
    Do While Not rsIn.EOF
      If rsIn.Fields("App") = rsOut.Fields("App") Then
        If rsIn.Fields("ID") = rsOut.Fields("ID") Then
        Else
          If rsOut.Fields("DateDiff") Is Null Then
            If rsOut.Fields("Date") > rsIn.Fields("Date") Then
              rsOut.Edit
              rsOut.Fields("DateDiff") = DateDiff("s", rsOut.Fields("Date"), rsIn.Fields("Date))
              rsOut.Update
            Else
               rsOut.Edit
              rsOut.Fields("DateDiff") = DateDiff("s", rsOut.Fields("Date"), rsIn.Fields("Date))
              rsOut.Update
            End If
          End If
        End If
      End If
      rsIn.MoveNext
    Loop
    rsOut.MoveNext
  Loop

ExitSub:
On Error Resume Next
  rsOut.Close
  rsIn.Close
  Set rsOut = Nothing
  Set rsIn = Nothing
  db.Close
  Set db = Nothing
  Exit Sub

ErrHandle:
  MsgBox "Error: " & Err.Number & ": " & Err.Description, vbCritical, "Error!"
  Resume ExitSub

End Sub

As for the error handling code, I tend to just add that in last, b/c if you use the default error handling, you can hit "debug" and go strait to the line where the error occurred - or close if not the exact cause of the problem.

I've not tested the code, so it definitely could have some quirks, but I thought it might work.. you'll just have to test and see.. if that's the route you want to go.






--

"If to err is human, then I must be some kind of human!" -Me

 

[DajTwo]

I wil definitely try this and debug if required.. But great thanks for providing the concept on how I should do this.

As far as the date diff, as I mentioned in my 1st post, this is not an issue as I worked on a code with the help of previous post here that calculates working minutes so that is not an issue.

I want to thank you for the time you spent on this..

 

[kjv1611]

No problem. I've been helped here LOTS myself over the past several years.

Just be sure to post back with your progress.

I'll be "out" literally for most if not all the day tomorrow and possibly Friday. Getting my wisdom teeth out.

I can't wait to have that over with!

--

"If to err is human, then I must be some kind of human!" -Me

 

[DajTwo]

Believe iot or not, I left the office early on Thursday to go fishing and only came back this morning.. Now i am inondated with stuff..

But surely less painfull than your trip at the dentist!!.

Will keep you up to date!\

Thanks