Crosstab design, year overlaps / combined

[trevorC via AccessMonster.com]

Hi All,
I am trying to create a crsstab query to output data. My problem is that when
this is run I get a combination of all Years for my data. eg - record data
for January is shown to sum all January data regardless of year, can a
crosstab seperate this into years as well as months.
eg
Jan 08 jan 09 feb 08 feb 09
1000 27 773 32
rather than just
jan feb
1027 805

do i need to first seperate the years and then process the data, if so how
can i recombine it into one crosstab result ?
thanks in advance.
Trevor.

 

[John Spencer MVP]

It would help if you posted your existing query (View: SQL).

It should be possible to return data ased on the month and year.

The Pivot clause of the crosstab would need to look something like the follwoing

PIVOT Format([SomeDate],"yyyy-mm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[trevorC via AccessMonster.com]

Thanks for the help, here's the SQL, very basic

TRANSFORM Count(gamrep.jobcount) AS CountOfjobcount
SELECT gamrep.[Part #], Count(gamrep.jobcount) AS [Total Of jobcount]
FROM gamrep
GROUP BY gamrep.[Part #], gamrep.[Customer Name]
PIVOT Format([In],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug",
"Sep","Oct","Nov","Dec");

 

[trevorC via AccessMonster.com]

I just tried this and get the correct result but there is not sorting of the
months in the output, looks like is A-Z sorting not by month, any idea's. How
it was with just the months it was ok.

TRANSFORM Count(gamrep.jobcount) AS CountOfjobcount
SELECT gamrep.[Part #], Count(gamrep.jobcount) AS [Total Of jobcount]
FROM gamrep
GROUP BY gamrep.[Part #], gamrep.[Customer Name], gamrep.[Customer Name]
PIVOT Format([In],"yyyy-mmm");

 

[John Spencer]

You will have to sort by yyyy-mm format. And part of the problem is that
as the records in gamrep increase to cover more and more dates, you will
not be able to use the query as the source for a report.

I believe Duane Hookom may have a solution for that problem, but I can't
find the reference for that right now.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================