Create a Multi-query Status Report with Report date as a Parameter passed to each query

[eriel.ramos]

I'm an Access newbie, so apologies.

I'm trying to emulate a Project Management Status Report I have in Excel.



The report is fed from 5 tables:

1. Charter - Descriptive data about project / PK=AutoID / Indexed on Project_ID
2. Tasks - Project activities / PK=AutoID / Related on Project_ID / Contains Report_Date field to indicate which Status report it should appear on
3. Issues - Recorded as identified, closed when resolved / PK=AutoID / Related on Project_ID / Contains Report_Date field
4. Risks - Recorded as identified, closed when resolved / PK=AutoID / Related on Project_ID / Contains Report_Date field
5. Action Items - recorded as identified, closed when completed/ PK=AutoID / Related on Project_ID / Contains Report_Date field

My goal is this:
+------------------------------------------------------------------------------------------------------------------------------+
| <Charter.Project name> <Charter.PM>................<Charter.Project ID>.......................... <input.ReportDate (Parameter)>............|

| [Block 1] .................................................. ......................| [Block 2].................................................. ...........................|
| <Tasks.Desc> where Tasks.ReportDate = input.ReportDate and | <Tasks.Desc> where Tasks.ReportDate = input.ReportDate and ... |
| Tasks.Completed = "Yes" and ............................................ | Tasks.Completed = "No" and .................................................. .|
| Tasks.ProjectID = Charter.Project ID ...................................| Tasks.ProjectID = Charter.Project ID ....................................... |

+-------------------------------------------------------------------------------------------------------------------------------+
| [Block 3] .................................................. ......................| [Block 4].................................................. ...........................|
| <Issues.Desc> where Issues.ReportDate = input.ReportDate OR | <Risks.Desc> where Risks.ReportDate = input.ReportDate OR .......|
| Issues.Status= "Open" and .................................................| Risks.Status = "Open" and .................................................. ..|
| Issues.ProjectID = Charter.Project ID ....................................| Risks.ProjectID = Charter.Project ID .......................................|

+-------------------------------------------------------------------------------------------------------------------------------+
| [Block 5] .................................................. ......................| [Block 6].................................................. ...........................|
|<Tasks.Desc> where Tasks.ReportDate <> input.ReportDate and | <Action.Desc> where Action.ReportDate = input.ReportDate OR....|
| Tasks.Completed = "No" and............................................... .| Action.Completed = "No" and............................................... ...|
| Tasks.ProjectID = Charter.Project ID ....................................| Action.ProjectID = Charter.Project ID .....................................|
+-------------------------------------------------------------------------------------------------------------------------------+

Ideally, all blocks could be the same size, but that's not a major point.

I've created queries which give me each block individually, and the related individual subreports.

However, I cannot get the integrated report to work; when invoked, I get asked the Report date for each query (6); if I place the subreports in the header, only the first 2 blocks appear; if I place them in the Detail section, I get multiple repeats of the same blocks.

QUESTION: Is what I need doable in Access? If so (as I suspect) How?

I'm at the end of my rope.

Thanks

Eriel Ramos-Pizarro, PMP

[June7]

Build a form that allows user input of criteria into controls (textbox, combobox, listbox).

Then have dynamic parameters in queries reference the control(s) instead of prompting popup. Example:

SELECT * FROM table WHERE ID=Forms!formname!controlname;

However, normally a properly designed report should not need the parameters repeated into all 6 queries. Filter the main report and only associated dependent records should display in subreports.