By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Multi-level GROUP BY clause is not allowed in subquery when opening report

Seth Schrock
Expert 2.5K+
P: 2,951
I have an incredibly complex query that works if I run just the query, but when I try to open a report based on the query, I get an error message that says "Multi-level GROUP BY clause is not allowed in subquery". I searched online and some people who had this error message fixed it by a second query to query the first query and base the report on the second query. That didn't work for me. Below is the query.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblACHFiles.ACHID, 
  2.  
  3. DCount("*","[tblACHFiles]","[ACHID] <=" & [ACHID] & " AND Format([EffectiveDate],'yyyymm') =" & Format([EffectiveDate],'yyyymm') & " AND [ACHCompanyID] = " & [ACHCompanyID]) AS FileCount, 
  4.  
  5. Format([EffectiveDate],'yyyymm') AS YearMonth, 
  6. tblACHFiles.EffectiveDate, 
  7. tblACHFiles.Entries, 
  8.  
  9. IIf([FileCount]>=
  10.      (SELECT NumToGetSpecialRate 
  11.       FROM tblCustomer 
  12.       WHERE CustomerID = Forms!frmInvoices!CustomerID),
  13.      (SELECT SpecialRate 
  14.       FROM tblCustomer 
  15.       WHERE CustomerID = Forms!frmInvoices!CustomerID),      
  16.      (SELECT PerFileCharge 
  17.       FROM tblCustomer 
  18.       WHERE CustomerID = Forms!frmInvoices!CustomerID)) AS Charge, 
  19.  
  20. [Charge]+[PerEntryCharge]*[Entries] AS TotalCharge, 
  21. tblCustomer.CustomerName, 
  22. tblCustomer.CustomerID, 
  23. tblCustomer.PerFileCharge, 
  24. tblCustomer.PerEntryCharge, 
  25. tblCustomer.SpecialRate, 
  26. tblCustomer.NumToGetSpecialRate, 
  27. tblTransType.TransType, 
  28. tblInvoices.BeginDate, 
  29. tblInvoices.EndDate, 
  30.  
  31. [tblCustomer].[CustomerName] & Chr(13) & Chr(10) 
  32. & "Attn: " 
  33. & [tblCustomer].[BillingAttnTo] & Chr(13) & Chr(10) 
  34. & IIf(IsNull([tblCustomer].[POBox]),[tblCustomer].[Address] & Chr(13) & Chr(10) & [tblCustomer].[City, State and Zip],[tblCustomer].[Address] & Chr(13) & Chr(10) & "PO Box " & [tblCustomer].[POBox] & Chr(13) & Chr(10) & [tblCustomer].[City, State and Zip]) AS CombinedAddress
  35.  
  36. FROM tblInvoices INNER JOIN ((tblCustomer INNER JOIN (tblFileTypes INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID) ON (tblCustomer.CustomerID = tblFileTypes.CustomerID) AND (tblCustomer.CustomerID = tblACHFiles.ACHCompanyID)) INNER JOIN tblTransType ON tblFileTypes.Credit_Debit = tblTransType.TransTypeID) ON (tblInvoices.InvoiceID = tblACHFiles.InvoiceID) AND (tblInvoices.CustomerID = tblCustomer.CustomerID)
  37.  
  38. WHERE (((tblACHFiles.InvoiceID)=[Forms]![frmInvoices]![InvoiceID]))
  39. ORDER BY Month(EffectiveDate), Day(EffectiveDate), tblACHFiles.ACHID;
  40.  
The line that I found to be the problem is line 20. If I make line 20 be
Expand|Select|Wrap|Line Numbers
  1. [PerFileCharge]+[PerEntryCharge]*[Entries] AS TotalCharge
then the report opens fine, but it is the wrong calculation. I tried just removing that line from the query and doing the calculation in the report, but I get the same error message. The field in the report that is bound to this line is not part of any GROUP. It is in the Data section of the report. I can't think of any other information that would help, but feel free to ask for clarification.

Oh, one more thing. The [Charge] field in line 20 is an alias that is calculated in lines 9 - 18.

Further testing: I just tried putting the whole IFF statement in place of [Charge] in line 20 incase the alias was throwing things off, but that didn't change anything. I didn't have much hope, but I thought it might be worth the try.
Jan 11 '12 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,709
I see nothing that either :
  1. Is explained by the error message.
  2. Explains why the query wouldn't work.

My suggestion would be to play with the SQL by chopping out as much as you can without losing the problem (IE. The resultant cut-down query must still fail in the same way.) and then posting that simplified SQL in the question.
Jan 11 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,951
The query does run, but the report based on the query won't open with the original line 20 and will if I replace line 20 with the code in the second code box. I will repost the slimmed down SQL shortly.
Jan 11 '12 #3

NeoPa
Expert Mod 15k+
P: 31,709
Ah. In that case you might want to look into the Sorting and Grouping part of the report. If any of those fields are included there this could explain both the presence of the problem as well as the actual error message. Check it out.
Jan 11 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,951
I just fixed it! I looked at the Sorting and Grouping and I didn't see anything that would affect it. So I looked at line 20 again I realized that the problem had to be in the calculation of [Charge]. I realized that I didn't need to do the subqueries in the IFF Then statement because I was already pulling NumToGetSpecialRate, SpecialRate, and PerFileCharge in the query. So I replaced the subqueries with the values that I was pulling and it worked! So here is the code that I ended up with:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblACHFiles.ACHID, 
  2.  
  3. DCount("*","[tblACHFiles]","[ACHID] <=" & [ACHID] & " 
  4. AND Format([EffectiveDate],'yyyymm') =" & Format([EffectiveDate],'yyyymm') & " 
  5. AND [ACHCompanyID] = " & [ACHCompanyID]) AS FileCount, 
  6. Format([EffectiveDate],'yyyymm') AS YearMonth,
  7. tblACHFiles.EffectiveDate, 
  8. tblACHFiles.Entries, 
  9.  
  10. IIf([FileCount]>=NumToGetSpecialRate,SpecialRate,PerFileCharge) AS Charge, 
  11. [Charge]+[Entries]*[PerEntryCharge] AS TotalCharge,
  12. tblCustomer.CustomerName, 
  13. tblCustomer.CustomerID, 
  14. tblCustomer.PerFileCharge, 
  15. tblCustomer.PerEntryCharge, 
  16. tblCustomer.SpecialRate, 
  17. tblCustomer.NumToGetSpecialRate, 
  18. tblTransType.TransType, 
  19. tblInvoices.BeginDate, 
  20. tblInvoices.EndDate, 
  21. [tblCustomer].[CustomerName] & Chr(13) & Chr(10) 
  22. & "Attn: " 
  23. & [tblCustomer].[BillingAttnTo] & Chr(13) & Chr(10) 
  24. & IIf(IsNull([tblCustomer].[POBox]),[tblCustomer].[Address] & Chr(13) & Chr(10) 
  25. & [tblCustomer].[City, State and Zip],[tblCustomer].[Address] & Chr(13) & Chr(10) 
  26. & "PO Box " & [tblCustomer].[POBox] & Chr(13) & Chr(10) & 
  27. [tblCustomer].[City, State and Zip]) AS CombinedAddress
  28.  
  29. FROM tblInvoices INNER JOIN ((tblCustomer INNER JOIN (tblFileTypes INNER JOIN tblACHFiles 
  30. ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID) 
  31. ON (tblCustomer.CustomerID = tblACHFiles.ACHCompanyID) 
  32. AND (tblCustomer.CustomerID = tblFileTypes.CustomerID)) 
  33. INNER JOIN tblTransType 
  34. ON tblFileTypes.Credit_Debit = tblTransType.TransTypeID) 
  35. ON (tblInvoices.CustomerID = tblCustomer.CustomerID) 
  36. AND (tblInvoices.InvoiceID = tblACHFiles.InvoiceID)
  37.  
  38. WHERE (((tblACHFiles.InvoiceID)=[Forms]![frmInvoices]![InvoiceID]))
  39.  
  40. ORDER BY Month(EffectiveDate), Day(EffectiveDate), tblACHFiles.ACHID;
  41.  
Jan 11 '12 #5

NeoPa
Expert Mod 15k+
P: 31,709
Well done Seth. As queries get more complicated it can be harder to cancel out what is not required.
Jan 11 '12 #6

Post your reply

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