Percentage change Calculation

J

James

I am trying to build a Query that uses 1 table to give Daily Temp changes

The table has daily temperatures for 22 cities.

Fields:
City Temp Date

I need a 4th field to calcualte the % change in Temp based on the previous day

(Todays Temp - Yesterdays temp)/Yesterdays Temp

Any idea's?
 
A

Allen Browne

Use a subquery to get yesterday's temp for the same city:

SELECT Table1.*,
Table1.Temp - (SELECT Temp FROM Table1 AS Dupe
WHERE ((Dupe.City = Table1.City)
AND (Dupe.Date = Table1.Date -1))) AS Delta
FROM Table1;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Top