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

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

 

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

 

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

 

[Marshall Barton]

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?

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 DCountfield,...) have to check each
record to see if the field has a Null in it.

 

[DaveF]

Thank you Marshall and Duane for your help with this issue.