E
etoucan
Guest
I would be really grateful if someone could please help me with this problem. 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 3data 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 and c) a count of all other milestones whose dates are in the future.
I've tried in vain to code this using crosstabs but I'm getting nowhere. I'm able to get 3 select queries to show the correct results for complete, late and due milestones, but I can't work out how to combine these into a crosstab. Help!
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 3data 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 and c) a count of all other milestones whose dates are in the future.
I've tried in vain to code this using crosstabs but I'm getting nowhere. I'm able to get 3 select queries to show the correct results for complete, late and due milestones, but I can't work out how to combine these into a crosstab. Help!
