Multi-level GROUP BY clause is not allowed in a subquery

Fozz

Registered User.
Local time
Today, 03:00
Joined
Mar 27, 2004
Messages
11
OS = Windows 2000, 5.00.2195, Service Pack 3
Access = 2000, 9.0.4402 sr-1

I have a table of weekly transactions of dollar amounts. Next, I created a query #1 to also show the year for the transaction using the YEAR function and show the last share price for the the year using a subselect (subquery). Query #1 showed the right data. This is the query:

SELECT year(t.date) AS TheYear, t.Date,
iif(t.transaction like "*employee*","Paid",iif(t.transaction like "*fee*","Expense","Appreciation")) AS TransactionType,
t.Transaction, t.Dollar, t.SharePrice, t.Shares, t.TotalShares,
(
SELECT Last(t2.SharePrice)
FROM FranklinTempleton AS t2
WHERE (((Year([t2].[date]))=year(t.date)) AND ((t2.Transaction) Not Like "*fee*"))
) AS LastSharePrice

FROM FranklinTempleton AS t;

A report using query #1 complained with this error message:
Multi-level GROUP BY clause is not allowed in a subquery.

I then created a query #2 which just did a "Select *" from query #1.

I changed the Data Record Source for the report to query #2 and then the report worked, sort of. It didn't show the results of the LastSharePrice field.

Further experimenting showed:

If I changed the report so that group headers and group footers were not shown, then LastShareprice did show up.

If I took query #2 and created a table from it, and used that table as the input for the report then, even with group header/footers turned on, the report worked fine.

I'll try some more experimenting and then see if I want to install the latest Office service pack.

This is a similar problem: http://www.access-programmers.co.uk/forums/showthread.php?t=60752&highlight=subquery

I think there is an Access/Jet bug/feature so I'm really posting this to share my experience. But if any of you folks can think of something I'm doing wrong, let me know.
 
I have already encountered the same thing, with a different group by type and I also overcame the problem using multiple queries. Three weeks later, when I looked back at it, it made sense and that makes sense.
 
I ran into the same kind of problem with this and resolved it quite simply by doing the following:

Original Query:
SELECT Customers.CustomerName, Invoices.InvoiceNumber, Invoices.Comment, Invoices.InvoiceDate, Invoices.InvoiceAmount, (SELECT Max([PaymentDate]) FROM PaymentLineItems WHERE PaymentLineItems.InvoiceLineItemSourceID=InvoiceLineItems.LineItemID) AS LastPaymentDate, (SELECT Sum([PaymentAmount]) FROM PaymentLineItems WHERE PaymentLineItems.InvoiceLineItemSourceID=InvoiceLineItems.LineItemID) AS TotalPayment, IIf(IsNull([LastPaymentDate]),Date()-[DueDate],([LastPaymentDate])-[DueDate]) AS Aging, IIf(IsNull([TotalPayment]),[InvoiceAmount],([InvoiceAmount])-([TotalPayment])) AS Balance FROM Customers LEFT JOIN (Invoices LEFT JOIN (PaymentLineItems RIGHT JOIN InvoiceLineItems ON PaymentLineItems.InvoiceLineItemSourceID=InvoiceLineItems.LineItemID) ON Invoices.InvoiceID=InvoiceLineItems.InvoiceSourceID) ON Customers.CustomerID=Invoices.CustomerSourceID GROUP BY Customers.CustomerName, Invoices.InvoiceNumber, Invoices.Comment, Invoices.InvoiceDate, Invoices.InvoiceAmount, Customers.CustomerID, Invoices.DueDate, InvoiceLineItems.LineItemID HAVING (((Invoices.InvoiceDate) Is Not Null) AND ((Customers.CustomerID)=22)) ORDER BY Customers.CustomerName, Invoices.InvoiceDate)

Then I changed the query using the SQL view and added the following
PREFIX OF NEW QUERY: SELECT * FROM (

SUFFIX OF QUERY: )

NEW QUERY GENERATED BY ACCESS:
SELECT *
FROM (SELECT Customers.CustomerName, Invoices.InvoiceNumber, Invoices.Comment, Invoices.InvoiceDate, Invoices.InvoiceAmount, (SELECT Max([PaymentDate]) FROM PaymentLineItems WHERE PaymentLineItems.InvoiceLineItemSourceID=InvoiceLineItems.LineItemID) AS LastPaymentDate, (SELECT Sum([PaymentAmount]) FROM PaymentLineItems WHERE PaymentLineItems.InvoiceLineItemSourceID=InvoiceLineItems.LineItemID) AS TotalPayment, IIf(IsNull([LastPaymentDate]),Date()-[DueDate],([LastPaymentDate])-[DueDate]) AS Aging, IIf(IsNull([TotalPayment]),[InvoiceAmount],([InvoiceAmount])-([TotalPayment])) AS Balance FROM Customers LEFT JOIN (Invoices LEFT JOIN (PaymentLineItems RIGHT JOIN InvoiceLineItems ON PaymentLineItems.InvoiceLineItemSourceID=InvoiceLineItems.LineItemID) ON Invoices.InvoiceID=InvoiceLineItems.InvoiceSourceID) ON Customers.CustomerID=Invoices.CustomerSourceID GROUP BY Customers.CustomerName, Invoices.InvoiceNumber, Invoices.Comment, Invoices.InvoiceDate, Invoices.InvoiceAmount, Customers.CustomerID, Invoices.DueDate, InvoiceLineItems.LineItemID HAVING (((Invoices.InvoiceDate) Is Not Null) AND ((Customers.CustomerID)=22)) ORDER BY Customers.CustomerName, Invoices.InvoiceDate) AS [%$##@_Alias];

I have no idea what the AS [%$##@_Alias] means, but it works wonderfully now for getting the subtotals by customer and report that I desperately needed.
 
thanks for MaxHewitt. I had same problem and googled it and found this topic now i solved the problem. great!
 

Users who are viewing this thread

Back
Top Bottom