Multi-level group by clause is not allowed in a subquery" error message

Thank you for looking at my question,

I am trying to run a report in MS Access that was originally falling over with the error message in the title - "multi-level group by clause is not allowed in sub-query".

The query that fed the report was built form three sub-queries, only one of which contained a Group By clause and another contained some IIF statements that included dynamic calculations.

I rejigged the query containing the Group By clause - that didn't remedy the situation and so I created a fourth query that just samples all of the data in the original query I was trying to report and used that to feed the report instead - no Group By clause and no calculations, just data but I'm still getting the same error message.

Can anybody tell me please what is the cause of this error?

The two queries are as follows:

''Query Driving Report
''qryReportData_Warehouse_Item_vs_Inventory_vs_Period_Issue

SELECT 

qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Item, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Item Description], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Warehouse, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Item WH Status],
 qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Unit, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Unit Units], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[On Hand], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[On Order], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Allocated, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[In Transit], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Blocked, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Committed, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[In Process], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Location Allocated], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Safety Stock], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[ERP Periods in Sample], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Days in Sample], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Summed Issue], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Sample Period Avg Issue], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Daily Average Issue], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Days Supply], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.Comments, 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Year From], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Period From], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Year To], 
qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data.[Period To]

FROM qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data;






''Query From which Report Query Data is extracted
''qryWarhouse_Item_vs_Inventory_vs_Period_Issue_Data

SELECT 
qryWarehouse_Item_Active_550021.Item, 
qryWarehouse_Item_Active_550021.[Item Description], 
qryWarehouse_Item_Active_550021.Warehouse, 
qryWarehouse_Item_Active_550021.[Item WH Status], 
qryWarehouse_Item_Active_550021.Unit, 
qryWarehouse_Item_Active_550021.[Unit Units], 
[qryWarehouse_Item _vs_Inventory].[On Hand], 
[qryWarehouse_Item _vs_Inventory].[On Order], 
[qryWarehouse_Item _vs_Inventory].Allocated, 
[qryWarehouse_Item _vs_Inventory].[In Transit], 
[qryWarehouse_Item _vs_Inventory].Blocked, 
[qryWarehouse_Item _vs_Inventory].Committed, 
[qryWarehouse_Item _vs_Inventory].[In Process], 
[qryWarehouse_Item _vs_Inventory].[Location Allocated], 
qryWarehouse_Item_Active_550021.[Safety Stock], 
qryWarehouse_Item_vs_Period_Issue_Data.[Sample Period] AS [ERP Periods in Sample], 
[Sample Period]*20 AS [Days in Sample], 
qryWarehouse_Item_vs_Period_Issue_Data.[Summed Issue], 
CLng(qryWarehouse_Item_vs_Period_Issue_Data.[Average Issue]*10^4)/10^4 AS [Sample Period Avg Issue], 
CLng([Summed Issue]/([Sample Period]*20)*10^4)/10^4 AS [Daily Average Issue], 

IIf([On Hand]=0,0,IIf([On Hand]+[Summed Issue]=0,0,IIf([On Hand]>0 And [Summed Issue]=0,9999,CLng(([On Hand]/([Summed 

Issue]/([Sample Period]*20)))*10^4)/10^4))) AS [Days Supply], 

IIf(qryWarehouse_Item_Active_550021.[Safety Stock]>[On Hand] And [Summed Issue]=0,"Safety Stock Level Exceeds 

Inventory;No Issue in Sample Period",
IIf([On Hand]>qryWarehouse_Item_Active_550021.[Safety Stock] And [Summed Issue]=0,"Inventory Exceeds Safety Stock 

Level;No Issue in Sample Period",
IIf(qryWarehouse_Item_Active_550021.[Safety Stock]>[On Hand],"Danger; Inventory Below Safety Stock Level",
IIF(qryWarehouse_Item_Active_550021.[Safety Stock]=0 AND [Summed Issue]>0 AND CLng(([On Hand]/([Summed Issue]/([Sample 

Period]*20)))*10^4)/10^4 < CLng([Summed Issue]/([Sample Period]*20)*10^4)/10^4,"No Safety Level but Days Supply Lower 

than Average Daily Issue","")))) AS Comments, 

qryWarehouse_Item_vs_Period_Issue_Data.[Year From], 
qryWarehouse_Item_vs_Period_Issue_Data.[Period From], 
qryWarehouse_Item_vs_Period_Issue_Data.[Year To], 
qryWarehouse_Item_vs_Period_Issue_Data.[Period To]

FROM 
(qryWarehouse_Item_Active_550021 INNER JOIN [qryWarehouse_Item _vs_Inventory] ON qryWarehouse_Item_Active_550021.Item 

= [qryWarehouse_Item _vs_Inventory].Item) INNER JOIN qryWarehouse_Item_vs_Period_Issue_Data ON 

qryWarehouse_Item_Active_550021.Item = qryWarehouse_Item_vs_Period_Issue_Data.Item
WHERE (((qryWarehouse_Item_Active_550021.Item) Like "1*") AND ((qryWarehouse_Item_Active_550021.[Item Description]) 

Not Like "*Wired*")) OR (((qryWarehouse_Item_Active_550021.Item) Like "2*") AND ((qryWarehouse_Item_Active_550021.

[Item Description]) Not Like "*Braze*")) OR (((qryWarehouse_Item_Active_550021.Item)>"2999999999"))
ORDER BY qryWarehouse_Item_Active_550021.Item;

Open in new window

Gary CroxfordOperations Support AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Check if this can help you
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary CroxfordOperations Support AnalystAuthor Commented:
Pointed me in the right direction, Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.