Calculating % Change between 2 Values in a Field Queried on Multiple Fields

[rahul76]

Hi,

I am a rank newbie to MS Access. Given below is an extract from a data that I have imported into a table:

City	MaxTemp	MinTemp	Date
New Delhi	36	32	09/23/2013
Mumbai	34	31	09/23/2013
New Delhi	40	38	06/30/2013
Mumbai	35	33	06/30/2013
New Delhi	24	20	11/30/2012
Mumbai	28	22	11/30/2012

I would like to calculate the % change in Max Temp between 2 dates for either a particular City and/or all cities between 2 dates sorted in descending order with the output format as follows:



New Delhi 10.21%

OR

New Delhi 10.21%
Mumbai 7.34%

I would appreciate anyone who could suggest a SQL Query for achieving the above results. Thanks in advance for helping out

[Dal Jeanis]

There are several questions that you need to answer.
1) Temperature change in percentage? Percentage of what?
2) Do these cities never reach zero degrees centigrade? I've included an IIF term in the SQL to make sure that the temperature you are dividing by never goes below 1 degree, to avoid blowing up on divide by zero, and also to avoid accidentally switching signs.
Here's the table names I used - you'll change the SQL to match your table and field names.

TempReads      Table name for Temperature reading table
   ReadCity    text - city name
   ReadDate    date/time - reading date
   ReadMaxtemp number - temperature on that date

Start with this SQL code and then change it to match your needs.

SELECT 
   T1.ReadCity, 
   Format( (t1.ReadMaxTemp/IIF(T2.ReadMaxTemp<1,1,T2.ReadMaxTemp))-1,"00.00%" ) AS PctChange,
   T1.ReadMaxTemp, 
   T1.ReadDate, 
   T2.ReadMaxTemp AS LastMaxTemp, 
   T2.ReadDate AS LastDate
FROM 
   TempReads AS T1, 
   TempReads AS T2
WHERE T1.ReadCity = T2.ReadCity 
AND T2.ReadDate = 
 (SELECT Max(T3.ReadDate) From TempReads AS T3 
  WHERE T3.ReadCity = T1.ReadCity 
  AND T3.ReadDate < T1.ReadDate);

Resulting in this output:

ReadCity   PctChange ReadMaxTemp ReadDate  LastMaxTemp LastDate
Mumbai       25.00%     35       30-Jun-13    28        30-Nov-12
Mumbai      -02.86%     34       23-Sep-13    35        30-Jun-13
New Delhi    66.67%     40       30-Jun-13    24        30-Nov-12
New Delhi   -10.00%     36       23-Sep-13    40        30-Jun-13