P
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 all in progress courses at any
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. so now, I can get
"In Progress" courses at any month.
However, the problem is that if I use (2), I have create a query for each
month and then combine thme. I want a single query to handle all months or in
other words I want to combine (1) and (2).
Regards
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 all in progress courses at any
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. so now, I can get
"In Progress" courses at any month.
However, the problem is that if I use (2), I have create a query for each
month and then combine thme. I want a single query to handle all months or in
other words I want to combine (1) and (2).
Regards