Multi-level GROUP BY clause is not allowed in a subquery (report only)

[Chad1984]

I'm not certain of whether I should post in here or in the queries forum. I decided to post here because this error ONLY appears when I try to view the report; the underlying queries work just fine.

The report is meant to show the proportions of ethnicities for each county.

I have a base query like so:

SELECT DISTINCT Zip.Cou_ID, County.Cou_Name, Person.Per_SSN, Ethnicity.Eth_Name
FROM State INNER JOIN (Ethnicity INNER JOIN (County INNER JOIN ((Person INNER JOIN (Household INNER JOIN Zip ON Household.Zip_Code = Zip.Zip_Code) ON (Household.Hou_ID = Person.Hou_ID) AND (Person.Per_SSN = Household.Hou_Per_SSN)) INNER JOIN [Case] ON Household.Hou_ID = Case.Hou_ID) ON County.Cou_ID = Zip.Cou_ID) ON Ethnicity.Eth_Name = Household.Eth_Name) ON State.Sta_Abbrv = Zip.Sta_Abbrv
WHERE (((Zip.Cou_ID) In (SELECT * FROM CountyReportFilter)) AND ((Case.Cas_Date)>=DateValue([Forms]![ReportLauncher]![StartDate].[Value]) And (Case.Cas_Date)<=DateValue([Forms]![ReportLauncher]![EndDate].[Value])));

Then a second query draws from the results:

SELECT EthnicityQuery.Cou_ID, EthnicityQuery.Cou_Name, EthnicityQuery.Eth_Name, Count(Per_SSN)/(SELECT Eth_Count FROM EthnicityCountQuery) AS Percentage
FROM EthnicityQuery
GROUP BY EthnicityQuery.Cou_ID, EthnicityQuery.Cou_Name, EthnicityQuery.Eth_Name;

This makes use of a third tiny query to aid in calculating the percentage (I only split this out to see if it would fix the error):

SELECT Count(Per_SSN) AS Eth_Count
FROM EthnicityQuery;

Then, finally, I make a report based on the second query (EthnicityReportQuery). It's supposed to show the county, ethnicity and percentage. This works just fine- until I try to group it by county. As soon as I add that grouping level, whenever I view the report I get the "Multi-level GROUP BY clause is not allowed in a subquery" error. Any ideas? This especially seems strange as I made another report and set of queries very similar (although ti didn't include the percentage or the DISTINCT keyword) which worked just fine.

 

[Darrylles]

Hi chad,

In report design view look at View -> Sorting and Grouping.

You may need to remove a selection from there.


ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Chad1984]

Uh, thanks... but I kind of want that grouping. The report works find if I use something like:

County    Ethnicity          Percentage
------    ---------          ----------
Otsego    Caucasian          88%
Otsego    African American   10%
Otsego    Hispanic           2%

Osceola   Caucasian          76%
Osceola   Hispanic           14%
Osceola   African American   10%

But what I WANT is:

County    Ethnicity          Percentage
------    ---------          ----------
Otsego
          Caucasian          88%
          African American   10%
          Hispanic           2%

Osceola
          Caucasian          76%
          Hispanic           14%
          African American   10%

In other words, I want that grouping level. How do I get the report to cooperate?

 

[dhookom]

It looks like your EthnicityCountQuery has only one record. You may need to use a slow query like:

SELECT EthnicityQuery.Cou_ID, EthnicityQuery.Cou_Name, EthnicityQuery.Eth_Name, Count(Per_SSN)/Min(Eth_Count) AS Percentage
FROM EthnicityQuery, EthnicityCountQuery
GROUP BY EthnicityQuery.Cou_ID, EthnicityQuery.Cou_Name, EthnicityQuery.Eth_Name;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Chad1984]

For future reference, I did find the answer. Or "a" answer, although it doesn't seem to work very well.

When in doubt, write more queries.

One query? Two? Three? That would have been simple enough, and worked for the other queries. I'd be embarrassed to say how many I actually ended up writing, and the logic is so convoluted that I'm not really sure how it works (especially at this time of night), but I finally got it working and won't mess with it.

Access complains about how you need to group a query? Don't group it- write a second query off the first and group THAT.

Get that wacky error about "multi-level group clauses"? Bury your queries several levels deep and the report will never know what you're up to.

So there is no problem that cannot be "solved" by writing an apparently endless stream of queries, all pulling data from each other- even though this seems to indicate a massive failing on the part of Access or of my own skill. Or to put it in other words:

An infinite number of queries at an infinite number of typewriters could product the full script for "Hamlet"- and lots of spaghetti code.

 

[Darrylles]

Chad,

Good of you to come back with eventual 'solution' - some don't bother.

I empathise with your multiple query 'feeds' - your emarrassment should not be borne alone - I've been forced to go 6-8 deep before now.

I'm sure that an SQL 'pro' would solve it, but alas - I can't afford to be a 'pro' in anything.

ATB

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.