Request urgent help on 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.

Thanks in advance!

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

 

[Lazc]

you would need the table that contains last years amounts.
but i guess it would be something like :

SELECT Table1.date, table1.item, table1.amout, table2.amount
from table1, table2
where table1.date = table2.date and table1.item = table2.item;

where table 2 contains last years amount

ciao !;-)

 

[Lazc]

oups sorry... i did not look well... i had not seen that the amount from last year was in the same table !

sorry

 

[Crowley16]

something like

select date as dteNow, item, amount,
(
select amount from table1 where year(date) = year(dteNow) - 1
) as amount_LastYear
from table1

you might have to fiddle a bit...

Crowley - as in like a Crow

 

[SkipVought]

tongaman,

You have just created a NON-NORMAILZED table!

http://www.abcdataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

What you want can be accomplished with the ORIGINAL table using a UNION query joining last year's and this year's values.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[OhioSteve]

If the date field actually has only one value per year, you can do what you describe. But you need to break down the problem into three parts.

First do a select query, Q1, that selects just the values from last year.

Then do a select query, Q2, that selects just the values form this year.

Finally, do a select query, Q3, that links Q1 with Q2 using item name and compares the prices.

This is not a very elegant solution, but I think that it will work.

 

[PHV]

Have you tried this ?
SELECT A.Date, A.Item, A.Amount, Nz(B.Amount,0) AS Amount_last_year
FROM Table1 AS A LEFT JOIN Table1 AS B ON (A.Date=DateAdd("yyyy",1,B.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