473,727 Members | 1,985 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Report Error: Multi-Level GROUP BY clause is not allowed in a subquery

A2003. I am getting this error message when I try to set a report's
recordsource to an SQL statement or a saved querydef that uses sub-queries.

I've debug.printed the SQL, and run it as a stand alone query, as a
rowsource for a list box and as a recordsource for a form. There's no
problem. I'm only encountering this in reports.

As a stop gap, I'm going to use a form for a printe report, bvut I
wonder if anyone has encountered anything like this before? A2003's
help is useless on this - no error number is shown and pressing the help
button with a broadband connection produces absolutely nothing.

The SQL is rather lengthy, but here're a couple of field's expressions
copied straight from the querydesign grid that could, I suppose, be
considered "typical". Remember, this works beautifully in forms and
listboxes and run as a query. There are 12 similar sub queries in the
SQL - they are drawing information from mainly a large transaction table
that contains many types of transactions (lots of fields) which are
either 0 or some value:

Square: IIf([Carried by]="Not Mounted",nz((Se lect top 1 ORD_SQUARE from
TBL_TURN_ORDERS as S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1
and ORD_TURN < 1) or ORD_TURN = 1) order by ord_sequence desc),"Not
Placed"),"-")

Sp Av: [UTY_SUP_CAP]+nz((select sum(ORD_SUP_COS T) from TBL_TURN_ORDERS
as S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1 AND ORD_TURN <
1) or ORD_TURN = 1)),0)

Carried by: IIf(nz((select top 1 ORD_TRANSPORT from TBL_TURN_ORDERS as S
where S.ORD_UNT_FK = A.UNT_PK and ORD_TRANSPORT <> 0 and
ORD_TRANS_UNT_F K <> 0 and ((S.ORD_TURN < 1 and S.ORD_CONFIRM = -1) or
S.ORD_TURN = 1) Order by ORD_SEQUENCE desc),-1)=-1,"Not Mounted",(SELEC T
top 1 UNT_CODE FROM TBL_TURN_ORDERS as S INNER JOIN TBL_UNITS as U ON
S.ORD_TRANS_UNT _FK = U.UNT_PK where S.ORD_TRANSPORT = 1 AND
S.ORD_UNT_FK = A.UNT_PK AND ((S.ORD_TURN < 1 AND S.ORD_CONFIRM = 1) or
(S.ORD_TURN = 1)) order by ord_sequence desc))
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #1
1 7197
Yes, Tim, it's very easy to get this error.

It seems that the reporter is creating another query into your query, to
handle the things specified in the Sorting'n'Group ing dialog. If there is
nothing there, the query with subquery works fine, but if it has to group
further, it spits the dummy with this "multi-level GROUP BY" message.

Since you generally can't avoid the grouping at the report level, the
workaround is generally to change the source query. You might be able to use
stacked queries instead of subqueries (i.e. a query that uses another query
as a source "table".) And you might even be able to get away with including
the subquery in the lower level query.

You may have also found that it's fairly easy to crash Access using
subqueries ("shut down by Windows... Sorry for inconvenience") . While
subqueries are very useful, JET's implementation of them is somewhat
lacking.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtherium> wrote in message
news:da******** **@coranto.ucs. mun.ca...
A2003. I am getting this error message when I try to set a report's
recordsource to an SQL statement or a saved querydef that uses
sub-queries.

I've debug.printed the SQL, and run it as a stand alone query, as a
rowsource for a list box and as a recordsource for a form. There's no
problem. I'm only encountering this in reports.

As a stop gap, I'm going to use a form for a printe report, bvut I wonder
if anyone has encountered anything like this before? A2003's help is
useless on this - no error number is shown and pressing the help button
with a broadband connection produces absolutely nothing.

The SQL is rather lengthy, but here're a couple of field's expressions
copied straight from the querydesign grid that could, I suppose, be
considered "typical". Remember, this works beautifully in forms and
listboxes and run as a query. There are 12 similar sub queries in the
SQL - they are drawing information from mainly a large transaction table
that contains many types of transactions (lots of fields) which are either
0 or some value:

Square: IIf([Carried by]="Not Mounted",nz((Se lect top 1 ORD_SQUARE from
TBL_TURN_ORDERS as S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1
and ORD_TURN < 1) or ORD_TURN = 1) order by ord_sequence desc),"Not
Placed"),"-")

Sp Av: [UTY_SUP_CAP]+nz((select sum(ORD_SUP_COS T) from TBL_TURN_ORDERS as
S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1 AND ORD_TURN < 1) or
ORD_TURN = 1)),0)

Carried by: IIf(nz((select top 1 ORD_TRANSPORT from TBL_TURN_ORDERS as S
where S.ORD_UNT_FK = A.UNT_PK and ORD_TRANSPORT <> 0 and ORD_TRANS_UNT_F K
<> 0 and ((S.ORD_TURN < 1 and S.ORD_CONFIRM = -1) or S.ORD_TURN = 1)
Order by ORD_SEQUENCE desc),-1)=-1,"Not Mounted",(SELEC T top 1 UNT_CODE
FROM TBL_TURN_ORDERS as S INNER JOIN TBL_UNITS as U ON S.ORD_TRANS_UNT _FK
= U.UNT_PK where S.ORD_TRANSPORT = 1 AND S.ORD_UNT_FK = A.UNT_PK AND
((S.ORD_TURN < 1 AND S.ORD_CONFIRM = 1) or (S.ORD_TURN = 1)) order by
ord_sequence desc))

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
7056
by: deko | last post by:
I can't move a multi-page report to the last record unless I keep the popup form (that defined it's subreports) open. DoCmd.OpenReport "rptStandard", acViewNormal DoCmd.Close acForm, "frmReportOptions" <== popup form This is the error I get when I try to move to the last page of the report *after* closing the popup: "This expression is typed incorectly, or is too complex to be evaluate...."
5
8302
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT statement. I count the records returned in the report by setting a text box to =Count(*). This works fine. Now I want to count the unique records in my report. I can dynamically create a SELECT statement that counts unique records. My statement looks like this: SELECT DISTINCT...
5
3283
by: dkelly925 | last post by:
Is there a way to add an If Statement to the following code so if data in a field equals "x" it will launch one report and if it equals "y" it would open another report. Anyone know how to modify this? Private Sub cmdPreview_Click() On Error GoTo Err_Handler 'Purpose: Open the report filtered to the items selected in the list box. 'Author: Allen J Browne, 2004. http://allenbrowne.com Dim varItem As Variant 'Selected items
2
1801
by: Arnold | last post by:
Greetings Gurus, In a report showing the names of students and their progress, I am getting an error in the name field (Name: #Error). The report gets its data from an unbound form containing two unbound textboxes, "txtStartDate" and "txtEndDate," and a multi-select listbox, "listName." I think the error occurs when there is no correct combination of data--for instance, no records for John Doe entered during this past week.
0
1063
by: klconklin | last post by:
I have a WAN network w/ Multi Web server hosting "Internal XML files" of the branch office proprietary app XML reports on each branches web server, I would like to be able to host on the Main server the report Application for all branches, I have all XML parsing and sorting working, but only if the exported branch XML file are with in the virtual root of the site, I would like to have the XML file hosted at each branch office and map a URL...
13
14936
by: Greg | last post by:
Most suggestions on this topic recommend to use a page footer and make it visible only on the last page. My problem is that the footer is half of the height of a page which means the detail would show on only the top half of each page with whitepace at the bottom of each page. How can I get the detail to fill each page with my "half page height" report footer at the bottom of the last page? Any suggestions to solve this would be...
1
2500
by: Intrepid_Yellow | last post by:
Hi, I have the following code that runs my report generator. The user selects a table from a combo box, then whatever fields they want from a list box. (This part all works and the report runs fine). There is then a combo box they can select a field from (eg CompanyID etc) and then the list box below that contains the values (eg Microsoft, Novell etc). These are all multi-select list boxes. Now I can get the code to work if the user...
4
4527
by: Fran | last post by:
I recently tried to use code for "Use a multi-select list box to filter a report" from Allen Browne in my database. I was able to add the code and adapt it to my needs, however I am getting an error. I click Ok and the report opens. I look in the design view and the filter is there but the IN equals "" (it is blank, the values are not in there)
1
4165
by: freetime | last post by:
Greetings, This is my first post so I will try to be brief and accurate. I have a report that uses 9 user selectable filters (Dates, Y/N's as Combo Boxes and 4 Multi-Select List Boxes. 2 of the Multi-Select Boxes acquire their data from other tables as Lookup's. All functions for the filters operate properly pulling the data needed. One of my options with the data is to send it to a Report that can be printed. The request...
12
4044
by: micarl | last post by:
How would i print a report based on criteria selected from several Combo Boxes as well as multiple Multi Select List Boxes, that are located on the same form? I can get one Multi List Box, just not several, to report using this code i found - Private Sub cmdPreview_Click()
0
8891
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9260
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9185
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9120
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8103
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.