Multi-level GROUP BY clause is not allowed in a subquery error message (1 Viewer)

[LALovelady]

When I try to create a report from my qryMeterDiff, I get the "Multi-level GROUP BY clause is not allowed in a subquery message." I'm pretty sure it has to do with the calculated fields in my qryMeterDiff (ScreenInDiff, ScreenOutDiff, MeterInDiff, MeterOutDiff). See rptMeters.

 

[arnelgp]

create a New query out of qryMeterDiff (see Query1) and use
it (Query1) in your report.

 

[jumnhy]

Yeah, this is what I was referring to earlier--if you "hide" the subquery-containing query under another simple select query, Access will let it work.

Inane.

 

[LALovelady]

create a New query out of qryMeterDiff (see Query1) and use
it (Query1) in your report.

Thanks so much!

 

[LALovelady]

Oops, I just realized another problem. When I run the report (rptMeters) based on qryMeterDiffHider, it won't show ScreenInDiff, ScreenOutDiff, MeterInDiff, MeterOutDiff. Why is this?

 

[jumnhy]

Clearly still an issue with the subqueries not being handled well--those are the fields computed by your subqueries. I don't have time to dig further at the moment but I'll take a look later. Guess the bandaid fix wasn't enough. Check out this page for further ideas in the interim: http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

Of those options, I think the most practical for you to run the report off of a temporary table. you can set up whatever event you're using to open the report to also first run a DELETE query on your temporary table, then an UPDATE/SELECT INTO query that inserts the records to the table from qryMeterDiff. That should run.

If I have time later I'll try to shoot you a functioning sample of what this would look like. I say this is your best bet though because I'm not seeing a simple way to carve out your subqueries, and obviously you'll need to use your grouping and sorting in the report once it's in production.

 

[LALovelady]

What I've done so far from your instructions. I so appreciate the help and I'm learning a lot!