Percent Change Query help

Question

My Director has asked me to generate a report showing the percent change in a value from month to month and also from quarter to quarter for certain locations. I have generated a query that gives me average value for each month for the two locations that I need.

Location	Month	MonthYear	Monthly Avg
Location 1	Jun-2013	201306	369.28
Location 2	Jun-2013	201306	288.45
Location 1	Jul-2013	201307	367.69
Location 2	Jul-2013	201307	289.00
Location 1	Aug-2013	201308	374.38
Location 2	Aug-2013	201308	295.17
Location 1	Sep-2013	201309	377.41
Location 2	Sep-2013	201309	305.32
Location 1	Oct-2013	201310	378.87
Location 2	Oct-2013	201310	316.83
Location 1	Nov-2013	201311	383.18
Location 2	Nov-2013	201311	323.04
Location 1	Dec-2013	201312	387.82
Location 2	Dec-2013	201312	324.89
Location 1	Jan-2014	201401	385.64
Location 2	Jan-2014	201401	330.82
Location 1	Feb-2014	201402	386.15
Location 2	Feb-2014	201402	347.73
Location 1	Mar-2014	201403	395.98
Location 2	Mar-2014	201403	346.30
Location 1	Apr-2014	201404	372.82
Location 2	Apr-2014	201404	350.45

I next tried creating a query that would give me the percent change. The SQL is as follows:

SELECT Monthly.Location, Monthly.Month, ((Monthly.[AvgOfSumOfAverage kW]/(SELECT TOP 1 Dupe.[AvgOfSumOfAverage kW]                 

FROM Monthly AS Dupe                     

WHERE Dupe.Location = Monthly.Location AND Dupe.MonthYear<Monthly.MonthYear))-1)*100 AS [Percent Change]

FROM Monthly;

The error I am getting is invalid argument to function. Can someone please tell me where I am going wrong here?

Answer 1

This should get you the monthly and previous averages. I would do the percent change in a text box in the report, not in the query:

SELECT Dupe.Location, Dupe.Month, Dupe.MonthYear, Dupe.MonthlyAvg,

(SELECT TOP 1 MonthlyAvg FROM Dupe D WHERE D.Location = Dupe.Location and D.MonthYear <Dupe.MonthYear ORDER BY MonthYear DESC) AS PrevMonthlyAvg

FROM Dupe;

Answer 2

The following is the SQL code for the Query named Monthly. I just updated the MonthYear that I am using to order the records by to be saved as a number. It did not help.

SELECT NDC_SDC_AverageTotal.Location, Format([NDC_SDC_AverageTotal].[Record Date],"mmm-yyyy") AS [Month], Format(Format([NDC_SDC_AverageTotal].[Record Date],"yyyymm"), "General Number") AS [MonthYear], Format(AVG(NDC_SDC_AverageTotal.[SumOfAverage kW]), "Fixed") AS [Average kW]

FROM NDC_SDC_AverageTotal

GROUP BY NDC_SDC_AverageTotal.Location, Format([NDC_SDC_AverageTotal].[Record Date],"mmm-yyyy"), Format([NDC_SDC_AverageTotal].[Record Date],"yyyymm")

ORDER BY Format(NDC_SDC_AverageTotal.[Record Date],"yyyymm");

Answer 3

So you have a field named "Month" (a function name) that actually stores a date value but is formatted somewhere to display like mmm-yyyy?

Have you tried placing [ ]s around Month?

Answer 4

The query shown has selected the Month field from a date field and formatted it as mmm-yyyy. Once you format it like that it seems to turn it into text although I don't know why.

Answer 5

Is Month a text field or a date field formatted as mmm-yyyy?

I would expect an ORDER BY clause in the subquery where you are selecting TOP 1.