Need help on a date query

[tongaman]

Can someone assist me to write a query on Table1 below that creates a new field that shows the cost for an item one year ago.

Table1.
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 1 year ago.

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

 

[willir]

tongaman

Perhaps something along the lines of...

Select * from YourTable
Where YourDateField = #(dateadd("yyyy", -1, YourDateField))#
+ other parts of the required Where clause

You will have to work on the syntax since you may not have provided all the info. You have to match the item for the current year to the item from last year.

(And please tell me you used a name for your date field other than using the reserved word "date")

 

[tongaman]

Willir,

Here is my sql below. Unfortunately, I am not an expert in SQL and I am not getting the desired result.

SELECT table1.Cost_Date, t1.Item, t1.Amount
FROM table1
Where Cost_date = (dateadd("yyyy", -1, Cost_date));

 

[willir]

How do you identify each record -- is it just by Item or is there a primary key?

 

[tongaman]

Willir,

There is no primary key - each record is identified by item.

Thanks....

 

[PHV]

Something like this ?
SELECT A.Cost_Date, A.Item, A.Amount, B.Amount
FROM Table1 AS A LEFT JOIN Table1 AS B ON (A.Cost_Date = DateAdd("yyyy",1,B.Cost_Date)) AND (A.Item = B.Item);


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244