Calculate diff between records of same field

[Pault123]

Hi All,

I am trying to calculate the change between records of the same field - i.e. the difference between Field1 with a date of 1/1/03 and Field1 with a date of 2/1/03 and between Field1 with a date of 2/1/03 and Field1 with a date of 3/1/03.

This "difference" could be calculated on the fly each time a report is run or calculated once and stored with the 2/1/03 and 3/1/03 records since they will never change. Below is a sample of what I'm trying to do. Any suggestions are greatly appreciated.

Thanks,

PaulT

Item_1

Date Inventory Change from Prev. Mo.

01/01/2003 550

02/01/2003 244 -306

03/01/2003 5 -239

04/01/2003 388 383

05/01/2003 278 -110

06/01/2003 305 27

07/01/2003 305 0

[-Queue-]

Are these first of the month dates unique or is there a possiblity of more than one record with a date of 04/01/2003, etc? If they are unique, then the attachment should work for you. If they are not unique, it should work as well, as long as you want the first record entered for that date.

FirstofMonthDiff.zip

[Pault123]

Queue,

I'm impressed, thanks very much for the solution.

The dates are unique within each item, although other items will have the

same dates but different quantities.

Thanks again,

Paul T.

[-Queue-]

If I understand correctly, you may need to change the calculation to include the item id. Otherwise the date may look up the wrong item info. If that's an issue, add a calc field equal to the item id & " " & date and change cPrevFOM to item id & " " & Date( Month( date ) - 1, 1, Year( date ) ). Then change the relationship to use the item id & " " & date calc field on the right-hand side. If it's not a problem, then just ignore this drivel.

[Pault123]

Thanks very much. Your help has been very useful.