Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Report Sum and Count Error: Multi-Level GROUP By Clause is not allowed in subquery

    Hi Everyone,

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



  2. #2
    Join Date
    May 2004
    New York State
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts