Creating table for charting purposes

Topic

New Reply

I have a table tracking projects that includes the following fields:

Project Number
Function
Due Date
Completion Date

I need to create several dashboard charts that show (by month), the number of accumulated projects due compared to the number created. I would like to have a record for each month (over several years) in the following format:

[Function] [Month/Year] [Count of Due dates] [Count of Competion Dates]
A Jan 2011 5 2
B Jan 2011 3 0
A Feb 2011 7 (2 more than Jan) 2 (no change)

The determinating of completed projects is based on data being entered into the field. All other field are populated.

Any thoughts/suggestions. The result (I hope) will be used to create line charts showing progress towards completing all tasks.

Reply | Quote

Replies

You can create two separate Totals queries, then combine their results in a third query.

The first would count([project numbers)] when grouped by Function and Month([Due Date]) and Year([due Date])
The second would count([project numbers)] when grouped by Function and Month([Due Date]) and Year([due Date]) where [completion Date] is not null.

Post back if you need help with actually creting these queries.

Reply | Quote

John, sorry for the late reply – I got off on some other projects for a bit. That is a great idea – I should be able to do that without too much effort (I’m still a novice at Access, but a dangerous one at that!). Thanks for the suggestion

Reply | Quote