Cross Tab Query with DateSerial

[Public]

Hi,
I am creating a cross tab query to capture data for each month of the year.
My query is similar to the following (This should give me all the completed
courses by month).
NOTE: I have numbered the queries for reference

(1)
TRANSFORM Count(Courses.CourseID) AS CourseCount
SELECT Year([Courses].[CourseCompletionDate]) AS Expr1
FROM Courses
WHERE Year([Courses].[CourseCompletionDate]) = 2008
GROUP BY Year([Courses].[CourseCompletionDate])
PIVOT Month([Courses].[CourseCompletionDate]) In
("1","2","3","4","5","6","7","8","9","10","11","12");

This was giving me good results. The problem is that I want a similar query
but now for "In Progress Courses". I will get in progress courses for a
specific month using the following formula

(2)
((Courses.[CourseBeginDate])<=DateSerial(2008,4,31)) AND
((Courses.CourseCompletionDate)>DateSerial(2008,4,31))

The above formula is giving me correct results for April.

However, the problem is that if I use (2), I have to create a query for each
month and then combine them. I want a single query to handle all months or in
other words I want to inject (2) into a query similar to (1).

 

[Duane Hookom]

April has only 30 days ;-)
I would create a group by query that create yyyymm for every month of every
year you need to query. Perhaps:

SELECT Format([CourseBeginDate],"YYYYMM") as YrMth
FROM Courses
GROUP BY Format([CourseBeginDate],"YYYYMM");

Then add this totals query to your Crosstab query with no joins. Set the
criteria to
WHERE YrMth Between Format([CourseBeginDate],"YYYYMM") AND
Format([CourseCompletionDate],"YYYYMM")

Then use [YrMth] as the Column Heading.