Subtract fields accross records

[Hold the Onions]

Using Access 2003, if you have a one table database,

Count Date Item Amount
1/22/08 Oranges 2
1/22/08 Apples 5
1/22/08 Pears 3
1/23/08 Oranges 4
1/23/08 Apples 8
1/23/08 Pears 6

How do you calculate the change in each item from the prior date as follows,

Count Date Item Amount Change
1/22/08 Oranges 2
1/22/08 Apples 5
1/22/08 Pears 3
1/23/08 Oranges 4 2
1/23/08 Apples 8 3
1/23/08 Pears 6 3

I am trying to use dlookup to pull the amount from the prior date, but it
does not seem to work within the same table. I have found solutions to other
questions where it seems to address this, but they are more complicated than
I can understand. Thanks.

 

[Wayne-I-M]

If you want to retreve the state of account on a previous date you will need
a table to store the "sales".
ID
Date
Linking field (to item table)
Number


If you just want to reduce the amount of items held in a certain field use
an afterupdate of an unbound field on the form.

 

[Klatuu]

I'm surpised you don't have a transaction table that records receipts and
sales of merchandise. Using that would be the most correct approach.

Also, you don't say where you want to perform this calculation. If you want
to show the value on a form view form, there is one answer. If you want to
show the value in a datasheet subform, there is a different answer. If you
want to display it on a report, there is still another answer similar to the
second, but slightly different. A bit more detail would help us answer your
question.