Multi-level Group By Clause is not allowed in a subquery

D

DaveF

I have the following subquery:

SELECT coin.coin
, coin.size & " " & coin.coin AS CoinSize
, coin.troyounces
, inventory.mintyear
, inventory.totalcost
, inventory.quantity
, inventory.quantity - (SELECT count(inventoryid) FROM disposal WHERE disposal.inventoryid=inventory.inventoryid) As NetQty
, location.location
, inventory.buydate
, inventory.locationid
, Format$([buydate],"dd mmmm yyyy",0,0) AS fBuyDate
FROM location INNER JOIN (coin INNER JOIN inventory ON coin.coinid = inventory.coinid) ON location.locationid = inventory.locationid WHERE inventory.locationid IN (2) AND coin.metal = 2;

I can execute this query without porblem. But when I use it as the RecordSource for a report I get the error:
Multi-level Group By Clause is not allowed in a subquery

I can eliminate the error by not referencing the NetQty field (calculated above with the sub-SELECT) in the Detail section of the report.

The report has no groupings, only the detail and page header and report footer sections.

Is there any work around that will allow me to reference this value in my report?
 
D

DaveF

PS

I tried creating a separate query and referencing the query as my report recordsource.

The query runs fine on its own but I get the same error.when I reference it in the report.


I have the following subquery:

SELECT coin.coin
, coin.size & " " & coin.coin AS CoinSize
, coin.troyounces
, inventory.mintyear
, inventory.totalcost
, inventory.quantity
, inventory.quantity - (SELECT count(inventoryid) FROM disposal WHERE disposal.inventoryid=inventory.inventoryid) As NetQty
, location.location
, inventory.buydate
, inventory.locationid
, Format$([buydate],"dd mmmm yyyy",0,0) AS fBuyDate
FROM location INNER JOIN (coin INNER JOIN inventory ON coin.coinid = inventory.coinid) ON location.locationid = inventory.locationid WHERE inventory.locationid IN (2) AND coin.metal = 2;

I can execute this query without porblem. But when I use it as the RecordSource for a report I get the error:
Multi-level Group By Clause is not allowed in a subquery

I can eliminate the error by not referencing the NetQty field (calculated above with the sub-SELECT) in the Detail section of the report.

The report has no groupings, only the detail and page header and report footer sections.

Is there any work around that will allow me to reference this value in my report?
 
D

Duane Hookom

This should work if InventoryID is numeric but it might be slow:
SELECT coin.coin
, coin.size & " " & coin.coin AS CoinSize
, coin.troyounces
, inventory.mintyear
, inventory.totalcost
, inventory.quantity
, inventory.quantity - dcount("inventoryid", "disposal", "inventoryid= " &
inventoryid) As NetQty
, location.location
, inventory.buydate
, inventory.locationid
, Format$([buydate],"dd mmmm yyyy",0,0) AS fBuyDate
FROM location INNER JOIN (coin INNER JOIN inventory ON coin.coinid =
inventory.coinid) ON location.locationid = inventory.locationid WHERE
inventory.locationid IN (2) AND coin.metal = 2;
 
M

Marshall Barton

DaveF said:
I have the following subquery:

SELECT coin.coin
, coin.size & " " & coin.coin AS CoinSize
, coin.troyounces
, inventory.mintyear
, inventory.totalcost
, inventory.quantity
, inventory.quantity - (SELECT count(inventoryid) FROM disposal WHERE disposal.inventoryid=inventory.inventoryid) As NetQty
, location.location
, inventory.buydate
, inventory.locationid
, Format$([buydate],"dd mmmm yyyy",0,0) AS fBuyDate
FROM location INNER JOIN (coin INNER JOIN inventory ON coin.coinid = inventory.coinid) ON location.locationid = inventory.locationid WHERE inventory.locationid IN (2) AND coin.metal = 2;

I can execute this query without porblem. But when I use it as the RecordSource for a report I get the error:
Multi-level Group By Clause is not allowed in a subquery

I can eliminate the error by not referencing the NetQty field (calculated above with the sub-SELECT) in the Detail section of the report.

The report has no groupings, only the detail and page header and report footer sections.

Is there any work around that will allow me to reference this value in my report?
Click to expand...


I think Duane's workaround will work so this is just FYI.

1) Using an Aggregate function (Count, Sum, etc) in a
report can also cause that error. Access creates its own
internal query based on your record source and the report's
design to use as the report's working record source and an
aggregate function requires a Group By in this query.

2) It is more efficient to use Count(*) or DCount("*",...)
than to count a specific field that is never null. The
aggregate functions all ignore Null field values so
Count(field) and DCount:)field,...) have to check each
record to see if the field has a Null in it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi-level GROUP BY clause not allowed in a subquery 3
Multi-Level GROUP By Clause is not allowed in subquery 4
Error -> Multi-level GROUP BY clause is not allowed in a subquery. 2
Multi-level GROUP BY clause is not allowed in a subquery? 2
Multi level Group By clause not allowed in subquery 1
Multi-Level Group By clause not allowed in subquery 3
Error 3612: Multi-level GROUP BY clause is not allowed in subquery 4
Multi-level group-by clause not allowed in a sub-query 2

Top