Unanswered: Report Sum and Count Error: Multi-Level GROUP By Clause is not allowed in subquery
I'm trying to sum a few columns and count the quantities of row on a report but, unfortunately the query has a subquery and the second I add a sum or a count in the footer it gives me the following error:
"Multi-Level GROUP By Clause is not allowed in subquery"
this is the query:
SELECT Commodities.Commodities, Invoices.[Delivery Date], Invoices.Program, Invoices.Province, Invoices.[Cost of Goods], Invoices.[Installation Cost], Invoices.Miscellaneous, Invoices.[Transportation Cost], (Select [Tax Rate] FROM tax WHERE Invoices.Province=tax.Region AND Invoices.[Delivery Date]>= tax.[Start Date] AND Invoices.[Delivery Date]<= tax.[End Date])
FROM (Commodities INNER JOIN [Program Information] ON Commodities.Commodities=[Program Information].CONTRACTDESCRIPTION) INNER JOIN Invoices ON [Program Information].[StandingOffer/Arrangement]=Invoices.Program
WHERE Commodities.Commodities Like ('*'+PROGRAM_NAME+'*') And Invoices.[Delivery Date]>=START_DATE And Invoices.[Delivery Date]<=END_DATE And (Invoices.[Cost of Goods]+Invoices.[Installation Cost]+Invoices.Miscellaneous+Invoices.[Transportation Cost])>=START_COST And (Invoices.[Cost of Goods]+Invoices.[Installation Cost]+Invoices.Miscellaneous+Invoices.[Transportation Cost])<=END_COST And Invoices.Program Like ('*'+SUPPLIER+'*');
Is there a tweak I can do to the query to make access think it's all in one table or a different way to add a calculation to the report that won't trigger that error?
(I'm trying to count the amount of records return and sum up "Cost of Goods", "Miscellaneous", "Installation Cost" and "transportation cost")
I always have that problem. To solve it, make the sub-query just that - a separate query, with all the criteria, joins, etc. necessary. Save it. Also, of course, remove the sub-query from the main query. Now, add the newly-designed query as a "table" into the main query and join correctly as needed.