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

[Seth Schrock]

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.

SELECT tblACHFiles.ACHID, 

DCount("*","[tblACHFiles]","[ACHID] <=" & [ACHID] & " AND Format([EffectiveDate],'yyyymm') =" & Format([EffectiveDate],'yyyymm') & " AND [ACHCompanyID] = " & [ACHCompanyID]) AS FileCount, 

Format([EffectiveDate],'yyyymm') AS YearMonth, 
tblACHFiles.EffectiveDate, 
tblACHFiles.Entries, 

IIf([FileCount]>=
     (SELECT NumToGetSpecialRate 
      FROM tblCustomer 
      WHERE CustomerID = Forms!frmInvoices!CustomerID),
     (SELECT SpecialRate 
      FROM tblCustomer 
      WHERE CustomerID = Forms!frmInvoices!CustomerID),      
     (SELECT PerFileCharge 
      FROM tblCustomer 
      WHERE CustomerID = Forms!frmInvoices!CustomerID)) AS Charge, 

[Charge]+[PerEntryCharge]*[Entries] AS TotalCharge, 
tblCustomer.CustomerName, 
tblCustomer.CustomerID, 
tblCustomer.PerFileCharge, 
tblCustomer.PerEntryCharge, 
tblCustomer.SpecialRate, 
tblCustomer.NumToGetSpecialRate, 
tblTransType.TransType, 
tblInvoices.BeginDate, 
tblInvoices.EndDate, 

[tblCustomer].[CustomerName] & Chr(13) & Chr(10) 
& "Attn: " 
& [tblCustomer].[BillingAttnTo] & Chr(13) & Chr(10) 
& 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

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)

WHERE (((tblACHFiles.InvoiceID)=[Forms]![frmInvoices]![InvoiceID]))
ORDER BY Month(EffectiveDate), Day(EffectiveDate), tblACHFiles.ACHID;

The line that I found to be the problem is line 20. If I make line 20 be

[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.

[NeoPa]

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.

[Seth Schrock]

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.

[NeoPa]

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.

[Seth Schrock]

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 NumToGetSpecial Rate, 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:

SELECT tblACHFiles.ACHID, 

DCount("*","[tblACHFiles]","[ACHID] <=" & [ACHID] & " 
AND Format([EffectiveDate],'yyyymm') =" & Format([EffectiveDate],'yyyymm') & " 
AND [ACHCompanyID] = " & [ACHCompanyID]) AS FileCount, 
Format([EffectiveDate],'yyyymm') AS YearMonth,
tblACHFiles.EffectiveDate, 
tblACHFiles.Entries, 

IIf([FileCount]>=NumToGetSpecialRate,SpecialRate,PerFileCharge) AS Charge, 
[Charge]+[Entries]*[PerEntryCharge] AS TotalCharge,
tblCustomer.CustomerName, 
tblCustomer.CustomerID, 
tblCustomer.PerFileCharge, 
tblCustomer.PerEntryCharge, 
tblCustomer.SpecialRate, 
tblCustomer.NumToGetSpecialRate, 
tblTransType.TransType, 
tblInvoices.BeginDate, 
tblInvoices.EndDate, 
[tblCustomer].[CustomerName] & Chr(13) & Chr(10) 
& "Attn: " 
& [tblCustomer].[BillingAttnTo] & Chr(13) & Chr(10) 
& 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

FROM tblInvoices INNER JOIN ((tblCustomer INNER JOIN (tblFileTypes INNER JOIN tblACHFiles 
ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID) 
ON (tblCustomer.CustomerID = tblACHFiles.ACHCompanyID) 
AND (tblCustomer.CustomerID = tblFileTypes.CustomerID)) 
INNER JOIN tblTransType 
ON tblFileTypes.Credit_Debit = tblTransType.TransTypeID) 
ON (tblInvoices.CustomerID = tblCustomer.CustomerID) 
AND (tblInvoices.InvoiceID = tblACHFiles.InvoiceID)

WHERE (((tblACHFiles.InvoiceID)=[Forms]![frmInvoices]![InvoiceID]))

ORDER BY Month(EffectiveDate), Day(EffectiveDate), tblACHFiles.ACHID;

[NeoPa]

Well done Seth. As queries get more complicated it can be harder to cancel out what is not required.