Current and Prior Year Query

[McArthurGDM]

How do I get current year and prior year information into one query result? That is, say I have a table that looks like this:

Years	Months	Data
2015	Jan	8
2015	Feb	1
2015	Mar	1
2015	Apr	4
2015	May	1
2015	Jun	1
2015	Jul	1
2015	Aug	1
2015	Sep	5
2015	Oct	1
2015	Nov	1
2015	Dec	1
2014	Jan	1
2014	Feb	2
2014	Mar	3
2014	Apr	1
2014	May	1
2014	Jun	1
2014	Jul	6
2014	Aug	1
2014	Sep	1
2014	Oct	1
2014	Nov	1
2014	Dec	1

And I want to create a query that displays results like this,

	Current Year	Prior Year
Jan	8	1
Feb	1	2
Mar	1	3
Apr	4	1
May	1	1
Jun	1	1
Jul	1	6
Aug	1	1
Sep	5	1
Oct	1	1
Nov	1	1
Dec	1	1

[lfpm062010]

Use the "Crosstab Wizard".

TRANSFORM Sum(Table1.Data) AS SumOfData
SELECT Table1.months
FROM Table1
GROUP BY Table1.months
ORDER BY Table1.months, Table1.Years DESC
PIVOT Table1.Years;

[ranman256]

Q1 = select [year] , [data] from table where [year] between year(date()) and dateAdd('yyyy',-1,[year])

Q2 = crosstab of Q1

[McArthurGDM]

Thanks,

I think you guys are right. The crosstab query is the approach here. How do I get JUST values, without any calculation in a crosstab query?

[lfpm062010]

Is there going to be more than one row per year and month combo? For example, for Jan, 2015, you have 2 rows of data of 8 and 5.

If it is, then the crosstab might not work right for you. There might a way to do it just using query. I have to reconstruct the query to post it. What would you do if there are more than one value per year and month combo.

[June7]

CROSSTAB aggregates data so more than one record for a year/month combo should not be an issue. Although, I am not seeing 2 records for Jan 2015 in the example. If there is only 1 record for each year/month, then calcs in CROSSTAB will not be issue - a SUM result will just be the values.

However, building a stable report to run perpetually based on CROSSTAB is not easy, especially with years as COLUMN HEADER (months as COLUMN HEADER would be more stable). So maybe instead http://allenbrowne.com/subquery-01.html#YTD