Unanswered: Not sure on how to proceed - do I need a Union query?
First time I've posted on here, so far I've been able to work out how to solve most of my problems with other people's posts but really can't work this one out.
We use an Access database (which I developed) for recording audit results and then generating reports. One record is created for each prescription audited and includes several fields, but those relevant to this issue are:
Ward (Lookup referring to another table)
Consultant (Lookup referring to another table)
Overall (A Yes/No box)
The ward and consultant tables record the ward/consultant name, the division* (Medicine/Surgery/Core Services/etc.) they belong to, the main site they work* at/site the ward is at (we have three hospitals), the consultant's speciality (e.g. orthopaedics), and the consultant's group (e.g. chest physicians at site A). There is one table that holds the ward divisions, another that holds consultant divisions, and so on for each of these fields.
I need to produce the following report:
M1 M2 M3
Group by Division
->Group by Hospital
--->Group by ward
then for each of months 1, 2, and 3 report the number of prescriptions audited and the percentage of those for which the overall box is ticked (i.e. value is -1)
I've managed to produce this as a static report, by producing two crosstabs, one to count the number of prescriptions and one to count the number of yeses. These are then combined to produce a query, which is used to produce the report.
However, I want to be able to produce the report using user-defined periods for each of M1, M2 and M3. So far I've managed to get the following steps to work:
1. Opening the report causes a form to pop up which prompts the user to enter the start and end dates for each of M1, M2, M3 (using calendar controls).
2. Three queries (one for each period) run to select the prescriptions for each period.
3. Three cross-tab queries run, one each based on the query in 2.
4. A further three queries produce a table (one for each period) listing the ward, the number of prescriptions and the number of prescriptions that are overall compliant.
This is as far as I've got; I need to combine the results of the three queries from step 4 to produce another query combining the results of all three tables. I think this needs a Union query, but I'm not sure how to do this, or if this is the best way to achieve what I need.