If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cross Tab Query with DateSerial
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). |
#2
|
|||
|
|||
Cross Tab Query with DateSerial
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. -- Duane Hookom Microsoft Access MVP "Public" wrote: 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). |
Thread Tools | |
Display Modes | |
|
|