473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Subquery problem

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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW [Hand Hygiene].Department,
  2.                    [Hand Hygiene].Date,
  3.                    [Hand Hygiene].HCWorker,
  4.                    [Hand Hygiene].Shift,
  5.                    Sum([Hand Hygiene].HW_AfterGlove)/Count([Hand Hygiene].Key)*100 AS Percentage,
  6.                    Count(*) AS [Count Of HandWashingAfterGloves],
  7.                    Avg([Percentage]) AS [Avg Of Percentage]
  8.  
  9. FROM               [Hand Hygiene]
  10.  
  11. GROUP BY           [Hand Hygiene].Department,
  12.                    [Hand Hygiene].Date,
  13.                    [Hand Hygiene].HCWorker,
  14.                    [Hand Hygiene].Shift
  15.  
  16. HAVING          ((([Hand Hygiene].Department) Like "*" & [What Department?] & "*"
  17.     Or            ([Hand Hygiene].Department) Is Null)
  18.    AND           (([Hand Hygiene].Date) Between [Start Date] And [End Date]
  19.     Or            ([Hand Hygiene].Date) Is Not Null)
  20.    AND           (([Hand Hygiene].HCWorker) Like "*" & [Health Care Worker] & "*"
  21.     Or            ([Hand Hygiene].HCWorker) Is Null)
  22.    AND           (([Hand Hygiene].Shift) Like "*" & [What Shift?] & "*"
  23.     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.
Jun 5 '09 #1
7 2264
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
Jun 5 '09 #2
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
Jun 5 '09 #3
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
Jun 5 '09 #4
@nsymiakakis
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.
Jun 5 '09 #5
NeoPa
32,556 Expert Mod 16PB
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])?
Jun 5 '09 #6
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.
Jun 6 '09 #7
NeoPa
32,556 Expert Mod 16PB
@nsymiakakis
Try removing the DISTINCTROW predicate of your SELECT clause. It shouldn't be there really anyway.
@nsymiakakis
Set the report's Record Souce property to the name of the query.
Jun 6 '09 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Maarten | last post by:
I've the following problem: Table1 serie | dir Table2 serie | user I am making a webpage in php to administrate the values in the db. What I want is that I select all series...
7
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into...
8
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
7
by: K. Crothers | last post by:
I administer a mechanical engineering database. I need to build a query which uses the results from a subquery as its input or criterion. I am attempting to find all of the component parts of...
5
by: Rod | last post by:
I have a client site where the code below has been working happily for at least four months. The site is using SQL Server 7. The code is ASP.NET Last week an error appeared related to the...
3
by: laurentc via AccessMonster.com | last post by:
Hi. I have an issue with my Access project. I have rather big tables of data (about 11 000 rows). These tables are historical product quotations, so they are very simple : - MyDate...
1
by: Docster2005 | last post by:
Hi folks, A DTS package we have run for years now no longer works. The specific part that is not working is a subquery in the SOURCE object of a transformation. The source is based on a...
13
by: ThePrinceIsRight | last post by:
I have a problem with using a subquery in MS Access. The purpose of the sub-query is to create a list of people who have had doctor exams in the past 6 months and exclude them from the main query....
1
by: mipo1984 | last post by:
I have subquery into a principal query, and i need the subquery return me only the last row of all results, but i can`t use "order by <field> desc " in the subquery because this return me an error,...
1
NeoPa
by: NeoPa | last post by:
Access QueryDefs Mis-save Subquery SQL Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "" and (often) add an extraneous...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.