Comparing pairs of records in a query

[lydonw]

I'm really hoping someone can help me with this. I've tried to get help in another forum and received absolutely ZERO response.

I need to identify pairs of records by their ID and date fields, then compare their number values.

I have a query with the following fields: AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits.

This query has many records for many different ConsumerIDs, and there is no limit to the number of records using a particular ConsumerID. Need to compare records in the following fashion:

1) Identify pairs of records where ConsumerID is the same AND AuthStart dates are successive.
2) Compare PSRUnits of these paired records to see what the numerical change has been between dates
3) Add the value of this change to a field called PSRChange
4) Repeat 2-3 for BSTUnits

I can give sample data, screenshot, or anonymized database, as needed. Any help is VERY appreciated

[recyan]

Till some one comes along,
can you elaborate on "ConsumerID is the same AND AuthStart dates are successive".
If possible, request you to post some dummy data & the result you expect, after running the query.

Thanks

[ssanfu]

sample data, screenshot,

Would be very helpful. Also expected results.

Are there always only (exactly) two records per ConsumerID?

I can see this taking a lot of code....