Compare previous row's data to current row's data

[phoenix81]

I have a task that will require me to subtract the current time from the previous time as long as the records fall within the same Extract Count. For example:
I have 8 transactions associated with ExtractCount #2 and 8 start/end & processing times. I need to subtract each row's time from the previous row's time. I have a conceptual idea of how to do it but need real help to figure out how to do this using a query.

Thanks,
Phoenix81

 

[Guus2005]

If you can identify a row it is possible to compare one with the other. Just select the two rows you want to compare.
Specifically if you want to subtract one record with a previous one it is more difficult because you usually don't know which record was selected. Sorting the query doesn't help. It helps when you use a recordset and determine what you want using VBA.

Success!

 

[gemma-the-husky]

the underlying problem is that access has no concept of a next or previous item

this is something that you arbitrarily dictate by imposing sequences on your data. you need to be really careful when making asumptions about ordering of records eg, what happens if one gets deleted or inserted in between calculations you have already done.

Can you not record the previous time (and therefore the elapsed time) when you save any given record. that way you dont need to refer to any other record

 

[The_Doc_Man]

I'll not go out on a limb and say this is flat out impossible. But it is a pain in the toches. As has been mentioned, "next" and "previous" have no absolute meaning in SQL context. Because IN THEORY, everything is done on the recordset as a whole. That is, it is supposed to be "monolithic" enough that you can't tell it was done sequentially. So... in that context, what's "next" ???

The answer is that in a VBA context, you can impose features not possible with simple Access SQL, or not trivial with simple Access SQL as the guiding method. In VBA, you can see the recordset elements one at a time and - in THAT context - determine next and previous based on the recordset's grouping and ordering clauses.

Search this forum for "NEXT RECORD" and "PREVIOUS RECORD" as topics. You'll see anything from short, ugly workarounds to long theoretical discussions of why you NEED workaroounds.

 

[WayneRyan]

Phoenix,

As mentioned earlier, this isn't a concept native to SQL.

Normally, you'd do these calculations when the data reaches a report or form.
Reports and forms have a sequential order to their displayed rows and are equipped
with the tools to do things such as running sums.

If you force a query to do this it isn't very elegant, but it's not difficult.

In a simple sense you can insert a new column in your query and use a Public Function
to retrieve the value. You can pass it your sorting information.

NewColumn: fnGetPreviousTime([ExtractCount])  <-- This establishes your "current" extract record
                                              <-- The function will get the previous using this.


Public Function fnGetPreviousTime (intExtractCount As Integer) As Date
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select LastTime " & _
                                  "From   YourTable " & _
                                  "Where  ExtractCount < " & intExtractCount & " " & _
                                  "Order By ExtractCount DESC")

If rst.EOF
   fnGetPreviousTime = 0
Else
   fnGetPreviousTime = rst!LastTime
End If


End Function

hth,
Wayne