Need help to write a query that returns a value for a given date

[salesi]

Can someone kindly assist me on writing a query on the current tabl
below that will return my desired result (also detailed below).

Current Table
Date Item Amount
1/april/2002 Car 100.00
1/april/2003 Car 200.00
1/april/2004 Car 300.00
1/april/2002 Truck 400.00
1/april/2003 Truck 500.00
1/april/2004 Truck 600.00

*** Desired Table - with a new field that shows the amount of an item
year ago.

Date Item Amount Amount_last_year
1/april/2002 Car 100.00 0
1/april/2003 Car 200.00 100.00
1/april/2004 Car 300.00 200.00
1/april/2002 Truck 400.00 0
1/april/2003 Truck 500.00 400.00
1/april/2004 Truck 600.00 500.0

 

[Van T. Dinh]

SELECT Main.ID, Main.Item, Main.EDate, Main.Amount,
Nz((SELECT Sub1.Amount
FROM Table1 As Sub1
WHERE (Sub1.Item = Main.Item) AND
(Sub1.Edate = (SELECT Max(Sub2.EDate)
FROM Table1 AS Sub2
WHERE (Sub2.Item =Main.Item) AND
(Sub2.EDate < Main.EDate)
)
)),0) AS LYAmount
FROM Table1 AS Main;

HTH
Van T. Dinh
MVP (Access)