I have a Parameter query with a few fields that people can filter with, Such as "Department", Date Range, Shift... This query is meant to sum the number of "Yes" answers as a percentage from a Y/N field. The query works fine and gives me all the filtered records depending on the choices in the query.
Here is my problem. I need to create a report that 1st, will display just the parameters they chose, such as "Accounting", 5/1/09-5/31/09, First Shift... and 2nd, give me a TOTAL Percentage for that whole month. Here is my Query:
Expand|Select|Wrap|Line Numbers
- SELECT DISTINCTROW [Hand Hygiene].Department,
- [Hand Hygiene].Date,
- [Hand Hygiene].HCWorker,
- [Hand Hygiene].Shift,
- Sum([Hand Hygiene].HW_AfterGlove)/Count([Hand Hygiene].Key)*100 AS Percentage,
- Count(*) AS [Count Of HandWashingAfterGloves],
- Avg([Percentage]) AS [Avg Of Percentage]
- FROM [Hand Hygiene]
- GROUP BY [Hand Hygiene].Department,
- [Hand Hygiene].Date,
- [Hand Hygiene].HCWorker,
- [Hand Hygiene].Shift
- HAVING ((([Hand Hygiene].Department) Like "*" & [What Department?] & "*"
- Or ([Hand Hygiene].Department) Is Null)
- AND (([Hand Hygiene].Date) Between [Start Date] And [End Date]
- Or ([Hand Hygiene].Date) Is Not Null)
- AND (([Hand Hygiene].HCWorker) Like "*" & [Health Care Worker] & "*"
- Or ([Hand Hygiene].HCWorker) Is Null)
- AND (([Hand Hygiene].Shift) Like "*" & [What Shift?] & "*"
- Or ([Hand Hygiene].Shift) Is Null));
I created a second query and the line works fine, and I get a total from the first query, but my problem there is, Reports only allow 1 query. When I added a subreport, I get the Parameters twice, and then it doesn't really work right.
Any help would greatly be appreciated.