Subquery problem

[nsymiakakis]

Hi All, I am have some trouble with a parameter query that I am hoping someone can guide me.
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:

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));

When I add the line: Avg([Percentage]) AS [Avg Of Percentage] I get an error "Subqueries cannot be used in the expression"

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.

[servantofone]

Add the Avg([Percentage]) to the report footer of your report in a textbox. This will solve your problems.

As far as dynamic reports go, I haven't tried it. This article looks like it might be of assistance to you and has the database examples for download at the bottom of the page.

** Link removed as per site rules - Please check **

-Kyle

[servantofone]

It just occurred to me that I might have misunderstood your question. Do you want to show a Total Average for all Departments on the report, but only list the individual departments that were filtered? If this is the case, create a second query that totals the percent of all departments (regardless of the parameters chosen), and then use a DLOOKUP to grab the Total Percentage from the new query. The other query can operate as you have it, only displaying the parameters chosen, because the total percentage will come from a different query. You can find more on DLOOKUP in the Access Help section. It should be pretty straightforward .

-Kyle

[nsymiakakis]

I will try the Footer Option, it sounds like it might work.
What I was looking for in the report, I created a Form Based Query, and when a user types in lets say:
Department: Education
Health Care Worker: MD
Shift: First
The query will currently pull up the filtered results with a field called Percentage. There may be 200 records in that query.
Rather than have all 200 records on the Report, I was hoping it would look like this:
Department: Education
Healthcare Worker: MD
Shift: First
Average: 95

The Average being the average of all 200 results.
I hope this clears it up.
Nick

[servantofone]

I will try the Footer Option, it sounds like it might work.
What I was looking for in the report, I created a Form Based Query, and when a user types in lets say:
Department: Education
Health Care Worker: MD
Shift: First
The query will currently pull up the filtered results with a field called Percentage. There may be 200 records in that query.
Rather than have all 200 records on the form, I was hoping it would look like this:
Department: Education Healthcare Worker: MD Shift: First Average: 95

The Average being the average of all 200 results.
I hope this clears it up.
Nick

It sounds like the footer option should work. Use an equals sign before the AVG function in the textbox. If your query is already set to group by the fields you are filtering for, the only thing you are missing is your Average Percentage.

[NeoPa]

Using the predicate DISTINCTROW is entirely superfluous when the GROUP By clause is used.

Having said that I don't see why it would cause the error you report. See Subqueries in SQL to understand what subqueries are about in SQL. Sometimes items in parentheses can be treated as subqueries, but I don't see anything like that here.

Can you say, did the query run ok without just that extra bit (Avg([Percentage]) AS [Avg Of Percentage])?

[nsymiakakis]

Yes, the query worked fine without the AVG, once I added it, I got the error.
I am going to try the Report Footer for the grand total, One other issue I am trying to work through, is, I made a form based query and I am not sure how to have the results go to a report.

[NeoPa]

Yes, the query worked fine without the AVG, once I added it, I got the error.
I am going to try the Report Footer for the grand total.

Try removing the DISTINCTROW predicate of your SELECT clause. It shouldn't be there really anyway.

One other issue I am trying to work through, is, I made a form based query and I am not sure how to have the results go to a report.

Set the report's Record Souce property to the name of the query.