Summing Values of a Subquery

[Jovan Johnson]

I am working on building a series of queries and reports based on a database so my associates can simply click on the reports and get important figures. I've run into a problem when it comes to aggregates based on a query with a subquery in it.

I'm used to using PHP and MYSQL to run queries and manage databases, so I'm new to Access syntax and limitations. I'm building these reports in Access 2010.

An example of the issue I'm running into can be seen with this query:

SELECT 	Mile1.Date,
		Mile1.[Card Number],
		Mile1.Vehicle,
		Mile1.Mileage,
		Mile1.Gallons,
		(Select 	Max(Mileage)
		
		 From 		Transactions
		 Where 		Mileage < Mile1.[Mileage]
			And 	Vehicle = Mile1.Vehicle) AS PrevMileage,
		[Mileage]-[PrevMileage] AS Elapsed,
		([Mileage]-[PrevMileage])/[Gallons] AS MPG,
		1/([MPG]/3.59) AS [Cost in Dollars Per Mile]

FROM 	Transactions AS Mile1

WHERE 	(((Mile1.[Card Number])=[Which Card Number?]));

This query works well to generate reports, but gives me "Multi-Level GROUP By Clause is not allowed in subquery" errors when I try to sum Elapsed Mileage or Average MPG and Cost. I tried to create aggregates at the query level but this resulted in syntax errors.

I've done some googling and it looks like I need to do something with multiple tables or queries, but I don't know enough about access to figure it out with unrelated examples. Any help would be appreciated!

[NeoPa]

I can't say that I can see why this would produce the error message posted (not that I doubt you, simply that the SQL looks good to me).

A solution may be to use DMax() instead, but I know that's a bit of a kludge. Frankly, you already have the SQL pretty much as I would try with my first stab, and I can't easily use trial and error to test any theories as I don't have the database (or anything similar enough to use). If you get nowhere by tomorrow let me know, and if I find it's a slack day I'll knock up a rig and see what I can discover for you.

You might try :

[Elapsed]/[Gallons] AS MPG,

... but I wouldn't expect that to make a difference.

[Rabbit]

I'm not certain but I don't have Access on this computer to test this theory; are you allowed to refer to a field you just created in the same SQL string? You may have to repeat the subquery where ever you refer to the alias.

[NeoPa]

I believe the answer to that is :
"Yes. Simple references in the SELECT clause are fine, but any references to such field names in other clauses (like WHERE, ORDER BY, GROUP BY, etc), because they are required before (and often to determine whether) the SELECT clause is processed, do not work (and require repeating of the calculation/formula used)."

[Jovan Johnson]

Thanks for the replies.

I've tried this suggestion and as suspected it still produces the error.

[Elapsed]/[Gallons] AS MPG,

When I try to add totals to the query I this syntax error upon running:
[IMGnothumb]http://img94.imageshac k.us/img94/9594/errortmn.png[/IMGnothumb]

[patjones]

Hi Jovan,

Can you post the query that is actually giving you the error? In your original post you laid out the query that works, but then you say "...to sum Elapsed Mileage or Average MPG and Cost. I tried to create aggregates at the query level but this resulted in syntax errors...". I'd just like to see exactly how you're trying to do the sum. Thanks.

Pat

[NeoPa]

I really can't see where you're going wrong, but I set up the following rig to see if I could see a problem with your logic or code. Other than tidying up a few sillies in your SQL (like the reuse of Elapsed in the MPG calculation) I simply set things up as it appears you had (I didn't have all the details from you so I had to guess some).

The following SQL worked first time without any issues :

SELECT [Date]
     , [Card Number]
     , [Vehicle]
     , [Mileage]
     , [Gallons]
     , (SELECT Max([Mileage])
        FROM   [Transactions]
        WHERE  ([Card Number] = Mile1.[Card Number])
          AND  ([Vehicle] = Mile1.Vehicle)
          AND  ([Mileage] < Mile1.Mileage)) AS [PrevMileage]
     , [Mileage] - [PrevMileage] AS [Elapsed]
     , [Elapsed] / [Gallons] AS [MPG]
     , 3.59 / [MPG] AS [Cost in Dollars Per Mile]
FROM   [Transactions] AS [Mile1]
WHERE  [Card Number] = [Which Card Number?]

The data I used in [Transactions] was :

[I][B]Card Number  Vehicle     Date    Mileage  Gallons[/B][/I]
  1             A     06/08/2011     32     1
  1             A     07/08/2011    100     3
  1             A     08/08/2011    150     4
  1             A     09/08/2011    195     6
  1             A     10/08/2011    250     8
  2             B     01/08/2011     50     1
  2             C     05/08/2011    145     3.5
  1             B     07/07/2011     20     0.5
  1             B     09/07/2011    100     2.5

The results I got were :

[I][B]             Card                                                         Cost in
   Date     Number  Vehicle  Mileage  Gallons  Mileage  Elapsed  MPG   Dollars Per Mile[/B][/I]
06/08/2011     1       A        32      1.0
07/08/2011     1       A       100      3.0       32       68   22.67      0.16
08/08/2011     1       A       150      4.0      100       50   12.50      0.29
09/08/2011     1       A       195      6.0      150       45    7.50      0.48
10/08/2011     1       A       250      8.0      195       55    6.88      0.52
07/07/2011     1       B        20      0.5
09/07/2011     1       B       100      2.5       20       80   32.00      0.11

[Rabbit]

From Allen Browne's website:

Error: "Multi-level group by not allowed"
You spent half an hour building a query with subquery, and verifying it all works. You create a report based on the query, and immediately it fails. Why?

The problem arises from what Access does behind the scenes in response to the report's Sorting and Grouping or aggregation. If it must aggregate the data for the report, and that's the "multi-level" grouping that is not permitted.

Solutions
•In report design, remove everything form the Sorting and Grouping dialog, and do not try to sum anything in the Report Header or Report Footer. (In most cases this is not a practical solution.)

•In query design, uncheck the Show box under the subquery. (This solution is practical only if you do not need to show the results of the subquery in the report.)

•Create a separate query that handles the subquery. Use this query as a source "table" for the query the report is based on. Moving the subquery to the lower level query sometimes (not always) avoids the problem, even if the second query is as simple as
SELECT * FROM Query1;

•Use a domain aggregate function such as DSum() instead of a subquery. While this is fine for small tables, performance will be unusable for large ones.

•If nothing else works, create a temporary table to hold the data for the report. You can convert your query into an Append query (Append on Query menu in query design) to populate the temporary table, and then base the report on the temporary table.


--------------------------------------------------------------------------------