Calculate variance between 2 records

[tractorvix]

Hi All,

I'm sure this has a really simple answer, but my brain just doesn't seem to be in gear today!

I have normalised my data to show:
Prod Date Value
A 1/1/8 4.5
A 1/2/8 5
A 1/3/8 5
B 1/1/8 6
B 1/2/8 5.75
B 1.3.8 5.5

What I need to do is calculate the monthly variance so it becomes
Prod Date Variance
A 1/1/8 0 (always zero)
A 1/2/8 -0.5 (5 - 4.5)
A 1/3/8 0 (5 - 5)
B 1/1/8 0 (always zero)
B 1/2/8 -0.25 (6 - 5.75)
B 1/3/8 -0.25 (5.75 - 5.5)

etc.

Can anyone please advise?

Thanks
Vicky

 

[tractorvix]

I've set up a seperate table to show the two dates matched up and have then been able to set up the following query:

SELECT tblNBaseRate.Scenario, tblNBaseRate_1.DateVal, [tblNBaseRate_1]![BaseRate]-[tblNBaseRate]![BaseRate] AS BRVariance
FROM tblNBaseRate AS tblNBaseRate_1 INNER JOIN (tblNBaseRate INNER JOIN [tbl-VarianceMatch] ON tblNBaseRate.DateVal = [tbl-VarianceMatch].Field1) ON (tblNBaseRate.Scenario = tblNBaseRate_1.Scenario) AND (tblNBaseRate_1.DateVal = [tbl-VarianceMatch].Field2);


which seems to be working.

Vicky