Calculation Between Multiple Records (Under Certain Conditions)?

[ajf200]

I'm working on a database used for tracking production. Currently, I have a query based off of multiple tables that looks like this:

Product__Date_____Shift___Throughput_Rejects
A...........12/4/06.....1.........1500...........5
A...........12/4/06.....2.........3500...........22
A...........12/4/06.....3.........4000...........29
B...........12/4/06.....3.........500.............8
B...........12/5/06.....1.........2000...........14


An example of something I need to calculate: What is the yield of of product A on Shift 3 on 12/4/06?

Yield = (Throughput - Rejects) / Throughput

But, if I simply took (Throughput - Rejects) / Throughput....that would give me the Yield To Date (for lack of a better term) because the Throughput and Rejects numbers are cumulative. They didn't actually make 4000 units that shift, they only made 500, bringing the cum. total to 4000.

The actual yield should be ((4000-3500)-(29-22))/(4000-3500).

But, it still has to be able correct for instances when the previous record has a different product (see Record 4 above). In that case, it should just take the current values in the record as is.

How would I go about doing this in Access/VBA?

Example of Logic for Throughput:
If (CurrentRecord.Product = PrevRecord.Product)
{
ThroughputPerShift = CurrentRecord.Throughput - PrevRecord.Throughput
}
Else
{
ThroughputPerShift = CurrentRecord.Throughput
}