Help with query(ies) for chart 1

[etoucan]

I hope someone can help me with this. I have the following fields in a Project Management table:

fldMilestoneID (Primary key)
fldProjectID (foreign key to Projects table)
fldForecastCompletionDate (Date/time)
fldRAGStatusID (Red/Amber/Green/Completed/Not started - Foreign key to RAGStatus table)

Each milestone has a forecast completion date, an associated project and its current RAG status. I would like to create a query to use to diaplay a graph on a report that shows the number of milestones, by project, that are not yet complete BUT should have been completed by now, i.e. today's date. Completed milestones always have a date less than or equal to today's, ie in the past).

The query should give 4 data items for each project - a) the count of all completed milestones up to today, b) the count of non-completed milestones, ie those with a RAG status of Red/Amber/Green/Not started, that are late, c) a count of all other milestones whose dates are in the future and d) a total for verification of accuracy.

I've tried in vain to code this but am getting nowhere. Help!

 

[PHV]

A starting point:
SELECT fldProjectID
,Sum(IIf(fldRAGStatusID='Completed' AND fldForecastCompletionDate<=Now(),1,0)) As [a)]
,Sum(IIf(fldRAGStatusID<>'Completed' AND fldForecastCompletionDate<=Now(),1,0)) As [b)]
,Sum(IIf(fldForecastCompletionDate>Now(),1,0)) As [c)]
,Count(*) As [d)]
FROM tblProjectManagement
GROUP BY fldProjectID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[etoucan]

Thnaks, PHV, that worked very well. I'd managed to bumble my own way to an answer using 4 separate queries and then using those in a fifth, but your method is much more elegant. Just shows there is more than one way to skin a cat. Thanks again.