Calculate time difference between different records

[skwilliams]

I have a database with a table linked to an excel file.

Here's an example of the data captured.

pkAuto Order Activity DTDATE DTHOUR DTMINUTE
19 1048994 NEW 1/11/2010 6 46
23 1048994 PROOFSNDCS 1/18/2010 8 4
25 1048994 PROOFSNDCS 1/26/2010 6 59
63 1049096 NEW 1/11/2010 7 27
78 1049124 NEW 1/11/2010 7 53
80 1049124 PROOFSNDCS 1/11/2010 10 41
82 1049124 PROOFSNDCS 1/13/2010 12 32
84 1049128 NEW 1/11/2010 7 56
86 1049128 PROOFSNDCS 1/19/2010 9 58
89 1049128 PROOFSNDCS 1/21/2010 10 20
94 1049128 PROOFSNDCS 1/21/2010 14 11
95 1049128 PROOFSNDCS 1/21/2010 14 12
102 1049166 NEW 1/11/2010 8 8
105 1049166 PROOFSNDCS 1/15/2010 13 15
106 1049166 PROOFSNDCS 1/19/2010 14 27
108 1049166 PROOFSNDCS 1/19/2010 14 39
111 1049166 PROOFSNDCS 1/20/2010 9 29
113 1049166 PROOFSNDCS 1/22/2010 13 29
127 1049193 NEW 1/11/2010 8 49
138 1049193 PROOFSNDCS 1/11/2010 15 5
142 1049193 PROOFSNDCS 1/12/2010 12 30

For each order, I need to figure the difference between the NEW activity and PROOFSNDCS activity and if the record has a 2nd PROOFSNDCS the time difference between it and the previous PROOFSNDCS activity.

Any ideas??

 

[odin1701]

Well first your time data is not in a standard date/time format, so it isn't something that a built in Access function could evaluate. You would need to convert the fields to a standard date/time format before you imported to access - or have some code that does this during the import. Then it's just a matter of taking the older date/time and subtracting the newer date/time, or using datediff() or something.

 

[The_Doc_Man]

In general, you can't do this easily with a query. (Not impossible, just ugly beyond belief.) This is because the underlying recordset has no order. There IS no previous record in a "pure" recordset. SQL actions are monolithic. That is, according to the SET theory on which SQL is based, everything happens to every selected query at the same time. The fact that the machine cannot do it that way doesn't stop the theory, and it also doesn't stop the implementers from making SQL appear as though it DID happen at the same time. For that reason, "Next record" and "previous record" have no fixed meaning.

The USUAL way to do this is a sorted query based on your order numbers, then write some VBA code to use a recordset based on the sorted query. VBA is capable of imposing order and differentiating between successive related records to compute the differences. You open the recordset based on the sort query, then process the records one at a time looking for the current record to be related to the previous one by having the same order number. Look VERY carefully at that sentence and you will see why this is such a bugaboo. It implies looking at two queries at once, but normal query actions are based on independence of records. And that's why the difficulty.

You can, if you wish, search the forum for "Next Record" in the context of queries. There are a few solutions that are strictly query based, but it is not one of the simpler things you could do.

 

[odin1701]

What if you grouped the order numbers and sorted them Ascending as well as sorting the date/time ascending? That should give you the order numbers all together, with the oldest record first. You still run into the Next Record thing.

 

[Brianwarnock]

Why not do it in Excel before the Import?

Just run a simple macro like the one below, or write a function to do the same.

Brian

Sub mysub()
'By BJW
' ID in ColA,Date colC,hours colD,Mins ColE, answer colG
'Highlight range in ColG

Dim days as long, hours as long, mins as long

For Each c In ActiveWindow.RangeSelection

If c.Offset(0, -6) = c.Offset(-1, -6) Then
days = DateDiff("d", c.Offset(-1, -4), c.Offset(0, -4))
hours = c.Offset(0, -3).Value - c.Offset(-1, -3).Value
mins = c.Offset(0, -2).Value - c.Offset(-1, -2).Value

    If mins < 0 Then
    mins = mins + 60
    hours = hours - 1
    End If
    
    If hours < 0 Then
    hours = hours + 24
    days = days - 1
    End If
    
c.Value = days & "  " & hours & " " & mins
Else
c.Value = 0
End If
Next c
End Sub