YTD and MTD Sales growth by activity

[dudumomo]

Hi there,



I'm trying to build a simple report to display the sales growth 2012 vs 2011 in YTD and MTD by activity (as rows) after receiving the daily information.

It is very simple with Excel but with Access...I don't really know how to do that with it.

Any help?

See an example of data enclosed.

Thank you for your help !

[June7]

Try:

A query for each year table that sums the Amount field and groups by Activity, Year, Month. Then join those two queries on the Activity and Month. This will place the Amount fields for each year side-by-side. Use that query as basis for further data manipulation. Following is all 3 queries as one nested SQL statement.

SELECT [2001Sum].Activity, [2001Sum].Month, [2001Sum].Year, [2001Sum].SumOfAmount, [2012Sum].Year, [2012Sum].SumOfAmount
FROM
(SELECT [Sales 2012].Activity, [Sales 2012].Year, [Sales 2012].Month, Sum([Sales 2012].Amount) AS SumOfAmount
FROM [Sales 2012]
GROUP BY [Sales 2012].Activity, [Sales 2012].Year, [Sales 2012].Month) As 2012Sum INNER JOIN
(SELECT [Sales 2011].Activity, [Sales 2011].Year, [Sales 2011].Month, Sum([Sales 2011].Amount) AS SumOfAmount
FROM [Sales 2011]
GROUP BY [Sales 2011].Activity, [Sales 2011].Year, [Sales 2011].Month) AS 2001Sum ON ([2012Sum].Month = [2001Sum].Month) AND ([2012Sum].Activity = [2001Sum].Activity);

This assumes both tables have the same activities in every month as well as the same months.

[dudumomo]

Hello June7.
Sorry for replying so late.

I understand the SQL query, however my main issue remains now how to display the result as I want. (See xls enclosed)

Is it possible to do so? How?

Thank you for your help !

[June7]

One approach is:

1. normalize data with a UNION query (must type or copy/paste into the SQL View of query designer)
SELECT Activity, [Year] & "/" & [Month] AS YM, Amount FROM [Sales 2011]
UNION SELECT Activity, [Year] & "/" & [Month], Amount FROM [Sales 2012];

2. use the UNION in a crosstab
TRANSFORM Sum([Amount]) AS SumOfAmount
SELECT [Activity]
FROM YearsUNION
GROUP BY [Activity]
PIVOT [YM];

3. do percentage calcs on report