Date data manipulation

[Duncan]

I have a set of attendance course dates that are spread out over a few years and I want to produce a report that has the months Jan-Dec across the top (column) and the years 2009-2012 down the side (row) the a count of people attending during that month for the data. Currently I’m not quite sure how to get the data using a query into a manageable format for the report.
I’m playing with a crosstab query. I have created two tables one with the years (2009-2012) to represent the Rows and a table with the months (Jan-Dec) to represent the columns. When I use the table with the dates in it the data goes all haywire.
Am I on the right track or have I completely missed the mark.

[recyan]

Just check out, if below gives some guidelines :

qryCourseAttendees

SELECT 
    tblCourseAttendees.CourseDate, 
    Year([CourseDate]) AS TheYear, 
    Month([CourseDate]) AS TheMonth, 
    tblCourseAttendees.CountOfAttendees
FROM 
    tblCourseAttendees;

The CrossTab Query :

TRANSFORM Max(qryCourseAttendees.CountOfAttendees) AS MaxOfCountOfAttendees
SELECT qryCourseAttendees.TheYear
FROM qryCourseAttendees
GROUP BY qryCourseAttendees.TheYear
PIVOT qryCourseAttendees.TheMonth;

Thanks

[Duncan]

Thanks. It worked perfectly first time.

[recyan]

Glad you found it helpful.

Thanks