?
Solved

Multi-level Group by Error

Posted on 2007-10-19
5
Medium Priority
?
214 Views
Last Modified: 2013-11-28
I have the following code that is in the on open event of a report. The problem is the recordsource sql statement I am sure. It is causing a error "Multi-level GROUP BY clause is not allowed in a subquery" Can somebody please let me know how to fix this?

strTopBottom = " DESC;"
strGroupBy = "GROUP BY CUNAME "
strOrderBy = "Order By sum(ORESTR)" & Left(strTopBottom, 5)
lblHeader.Caption = "Top " & strCriteria & " Customers"
strRecordSource = "SELECT TOP " & strCriteria & " CUNAME, Count([ORODR#]) AS OCount, Sum(ORESTR) AS ORev, SUM(ORMILE) AS OMiles, Sum(DIEMIL) AS EMiles,sum(DITMIL) as Tmiles, (Select count([ORODR#]) from CustomerTop10 where [ORDV#] in ('097','098','099')) AS BCount, (Select count([ORODR#]) from CustomerTop10 where [ORDV#] not in ('097','098','099')) AS CCount FROM CustomerTop10 " & strGroupBy & strOrderBy
Me.RecordSource = strRecordSource


0
Comment
Question by:UniqueData
  • 2
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20111842
UniqueData,
try first, removing the subquery from the strRecordSource and see what you can come up to.
0
 
LVL 7

Author Comment

by:UniqueData
ID: 20111957
That is the problem I know. Is there anything I can do about it or can sub queries not allowed in a report?
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 20112973
When I try your lines, I get something like this:

SELECT TOP <*missing*>
    CUNAME,
    Count([ORODR#]) AS OCount,
    Sum(ORESTR) AS ORev,
    SUM(ORMILE) AS OMiles,
    Sum(DIEMIL) AS EMiles,
    sum(DITMIL) as Tmiles,
    (Select count([ORODR#])
        from CustomerTop10
        where [ORDV#] in ('097','098','099')
    ) AS BCount,
    (Select count([ORODR#])
        from CustomerTop10
        where [ORDV#] not in ('097','098','099')
    ) AS CCount
FROM CustomerTop10
GROUP BY CUNAME
Order By sum(ORESTR) DESC

Try this instead:

SELECT TOP <*missing*>
    CUNAME,
    Count([ORODR#]) AS OCount,
    Sum(ORESTR) AS ORev,
    SUM(ORMILE) AS OMiles,
    Sum(DIEMIL) AS EMiles,
    sum(DITMIL) as Tmiles,
    -Sum([ORDV#] in ('097','098','099')) AS BCount,
    -Sum([ORDV#] not in ('097','098','099')) AS CCount
FROM CustomerTop10
GROUP BY CUNAME

This isn't exactly the same. Not BCount and CCount will be different for each row, while you wanted the total each time. But you can obtain that from the report directly (compute the sum in the report footer).

I tried something similar to your query, and it does work. But then you might have additional groupings in your report, or additional groupings in the query CustomerTop10 (this doesn't sound like a table, does it?). Are you really trying to get the top X from a top 10 query?

We might need information from upstream and downstream...

(°v°)
0
 
LVL 7

Author Comment

by:UniqueData
ID: 20126760
I have never seen the last couple of lines you put in. The -Sum. That works like a charm and I dont have to do embedded sql statements that Access reports do not support. Thank you.
0
 
LVL 58

Expert Comment

by:harfang
ID: 20128819
Glad you like it. Since a boolean field or expression returns 0 or -1, the Sum() would be negative. Hence the -Sum().
Success with your project!
(°v°)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question