Hi Folks,
I have been banging my head on this for a while now and am asking from some advice.
I have a Project Completion Report that pulls information from a number of tables:
- Program (PK: ID)
- Project (PK: ID, FK: Program_ID constrained to Program.ID)
- Project_Monthly (PK: ID, FK: Project_ID constrained to Project.ID)
- Status_Report (PK: ID, FK: Project_ID constrained to Project.ID)
The Status_Report table has a Record_Date field and holds a BLOB with the status reports for all the projects. In the best of all possible worlds, every project would have a status report, but, obviously, some have no status reports.
I have been asked to extend the Project Completion Report. It currently shows:
- Program Number (Program.Program_Number)
- Program Name (Program.Program_Name)
- Project (Project.Project_Name)
- Projected Completion Date (Project_Monthly.Current_Date_Production)
We would like to add the latest status report. So I started with something that works in another report, which is to get the ID from the Status_Report table for the record that has the maximum date value in the Record_date that matches the Project in the current Program. But this other query is looking at one program at a time. The Project Completion Report is showing all programs with projects that complete within a certain time frame.
The requirement is to show all projects that will complete within a time frame and to display the ID of the most recent status report (I use javascript to turn this into a downloadable link). However, whenever I try something like:
...
AND Status_Report.Record_Date =
(select MAX(Status_Report.record_date) from Status_Report
where Status_Report.ID = Project.ID)
I loose all projects that have no status reports associated with them.
How can I keep all the projects that fulfill the criteria of completing within a specified time frame and add the ID, if it exists, of the most recent status report?
I thank you for your time and assistance. Please let me know how I can clarify the problem more clearly.
Yours,
Petie