Subtract amounts from two records in same table

[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.

 

[Allen Browne]

Use a subquery to look up the matching preceeding record.

Details in:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Lord Kelvan]

ok you can do it via a query but you need three queries

to make these queries you need to make a new query in design view then
click on view in the menu bar then click on sql view delet what is
there and paste the code then save it as the name i tell you to save
it as then repeat for the next one

query 1 will give you the item amounts

SELECT mytable.[Count Date], mytable.item, mytable.amount, mytable.id
FROM mytable;

save this as qryitemamount

the next query will giev you the same data but will take one off the
date this is so it can compare the current value with the value from
the day before

SELECT mytable.id, mytable.[Count Date], [Count Date]-1 AS Expr1,
mytable.item, mytable.amount
FROM mytable;

and save this as qryitemamount-1

the final query is where the magic happens it takes the values in
query1 and subtracts them from query 2 based on the difference in the
dates

SELECT [qryitemamount-1].[Count Date], [qryitemamount-1].item,
[qryitemamount-1].amount, [qryitemamount-1].amount-
qryitemamount.amount AS change
FROM qryitemamount RIGHT JOIN [qryitemamount-1] ON (qryitemamount.
[Count Date] = [qryitemamount-1].Expr1) AND (qryitemamount.item =
[qryitemamount-1].item);

and save this as what ever you want as this is the query you run to
get that information

as a note that query will give you negative values when the current
value is below the day befores 1

if you dont want negatives

SELECT [qryitemamount-1].[Count Date], [qryitemamount-1].item,
[qryitemamount-1].amount, IIf([qryitemamount-1].[amount]-
[qryitemamount].[amount]<1,"",[qryitemamount-1].[amount]-
[qryitemamount].[amount]) AS change
FROM qryitemamount RIGHT JOIN [qryitemamount-1] ON (qryitemamount.
[Count Date] = [qryitemamount-1].Expr1) AND (qryitemamount.item =
[qryitemamount-1].item);


that query will only show positive values

hope this helps

Regards
Kelvan