Compare fields in different records for changes

[lydonw]

I have a table called tblAuthorizations. It appears as follows

AuthInstanceID....ConsumerID....AuthNumberID....AuthStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102..........4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102..........1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863..........10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788..........4/22/13......7/21/13....0..............520
282523535...........469432..........20254788..........1/21/13.......4/21/13....0.............520


I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.

What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.

I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.

I've tried adding the following fields, but this isn't working.

PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])

PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])


Apologies if this is crazy long and still manages to be confusing, but I've been failing at this for a couple days and would appreciate ANY help whatsoever.

 

[lydonw]

Looking around I haven't found a solution, but I think I can better define what I need.

I need to match each consumer's most recent authorization with their previous one(defined by same consumerID and a max AuthEnd that is less than most recent AuthStart). BSTUnits must be compared for these two records, and results in either a "Loss" or "Gain." Same needs to be done for PSRUnits.

 

[lydonw]

Another attempt to clarify my question in the hopes someone can help.

Imagine there are four fields A, B, C, D

I need to compare records based on the following

where two records have values for A that are equal, 1st Record is most recent date B, the 2nd is the previous date B. Compare number value C between two records. If 1c > 2c then "Gain," if 1c<2c then "Loss," if there is no second record to compare then "null"

There will be many instances where I need to compare pairs of records in this way