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!
7 3279 NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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)."
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:
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 32,556
Expert Mod 16PB
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 : - Card Number Vehicle Date Mileage Gallons
-
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 : - Card Cost in
-
Date Number Vehicle Mileage Gallons Mileage Elapsed MPG Dollars Per Mile
-
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
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.
--------------------------------------------------------------------------------
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Yaroslav Bulatov |
last post by:
I made an array of 10 million floats timed how long it takes to sum
the elements, here's what I got (millis):
gcc -O2: 21
Python with numarray: 104
Python with Numeric: 302...
|
by: The alMIGHTY N |
last post by:
<store>
<frequent_shopper_discount value="5"/>
<premium_member_discount value="10"/>
<inventory>
<item>
<msrp value="3.99"/>
</item>
<item>
<msrp value="2.78"/>
</item>
|
by: neeraj |
last post by:
Hi
Can any body give me the syntax for summing the elements of the an
array , without looping
thanks
|
by: Prashantsd |
last post by:
I would like to sum the values from combo box and get the result in text box.
I have three Combo box which has 2 columns each (Options and Ratings)
Options (Ratings)
Strongly Agree (50)
Agree...
|
by: JHNielson |
last post by:
I Know I've posted an Urgent message before. But I'm in the middle of system testing, and these little stupid bugs are killing me......
I have a query that checks that a set of values (the...
|
by: mjta |
last post by:
Can I sum the values from a list box. If I use cascading list boxes, can I sum the values in a text box automatically when the list is updated?
|
by: sillyr |
last post by:
I have a form where I would like to include a text box that shows the sum of values entered into the form. The form collects information about length by having fields with separate centimeter...
|
by: VbaNewbee |
last post by:
I have a form with a few filters. Once the user clicks "search button", the code first evaluates my filters, then shows the query results in a List Box" titled backschedule.
I have a few text...
|
by: OllyJ |
last post by:
Hi guys hope you can help
I have a text box for each day of the week in a form and numbers are entered into them (but sometimes they need to be left blank i.e. not 0). I am summing these values...
|
by: KingKen |
last post by:
I have a report which I am pulling from a crosstab query and want to calculate the values at the end of each row. This report has 5 columns named col1, col2... col5. Col6 is where the total goes. Can...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |