G
gmann425
I need to create a line chart that displays the number of a project's
due dates for each month. There are 2 data series - original due dates
and revised due dates.
To do this, I am querying a table for 3 columns:
The month (jan - dec)
The count of original due dates per month
The count of revised due dates per month
I'm also querying on the last update date because data is appended, not
overwritten whenever it is loaded in so there can be duplicates.
My query:
SELECT Format([due_date],'mmm') AS Planned, Count(Planned) AS
CountPlanned, Format([revised_date],'mmm') AS Actual, Count(Actual) AS
CountActual
FROM Tbl_Milestones
WHERE .update_date=#9/6/2006#
GROUP BY Format([due_date],'mmm'), Format([revised_date],'mmm')
ORDER BY Format([due_date],'mmm');
Produces these results:
Planned CountPlanned Actual CountActual
Aug 2 Aug 2
Aug 1 Sep 1
Feb 2 2
Jul 1 Jul 1
Jun 2 Jun 2
Mar 2 Mar 2
May 4 May 4
Sep 1 Oct 1
How I want it to look:
Month CountPlanned CountActual
Jan 0 0
Feb 2 0
Mar 2 2
Apr 0 0
and so on...
Can the months be printed in order like above (even when there are no
dates for a month)?
Can I combine the 2 counts into one query?
Thank you very much for any help.
due dates for each month. There are 2 data series - original due dates
and revised due dates.
To do this, I am querying a table for 3 columns:
The month (jan - dec)
The count of original due dates per month
The count of revised due dates per month
I'm also querying on the last update date because data is appended, not
overwritten whenever it is loaded in so there can be duplicates.
My query:
SELECT Format([due_date],'mmm') AS Planned, Count(Planned) AS
CountPlanned, Format([revised_date],'mmm') AS Actual, Count(Actual) AS
CountActual
FROM Tbl_Milestones
WHERE .update_date=#9/6/2006#
GROUP BY Format([due_date],'mmm'), Format([revised_date],'mmm')
ORDER BY Format([due_date],'mmm');
Produces these results:
Planned CountPlanned Actual CountActual
Aug 2 Aug 2
Aug 1 Sep 1
Feb 2 2
Jul 1 Jul 1
Jun 2 Jun 2
Mar 2 Mar 2
May 4 May 4
Sep 1 Oct 1
How I want it to look:
Month CountPlanned CountActual
Jan 0 0
Feb 2 0
Mar 2 2
Apr 0 0
and so on...
Can the months be printed in order like above (even when there are no
dates for a month)?
Can I combine the 2 counts into one query?
Thank you very much for any help.