Multi-Level GROUP BY clause not allowed in a subquery 
Author Message
 Multi-Level GROUP BY clause not allowed in a subquery

I get the following error message in a report I am trying
to construct:

"Multi-Level GROUP BY clause not allowed in a subquery"

This is the SQL statement the query is based on:

SELECT
tblPeople.*,
tblDetails.*,
 (SELECT tblRankData.Rank
  FROM tblRankData
  WHERE tblRankData.Grade = tblPeople.Grade)
 + " " + tblPeople.FirstName
 + " " +tblPeople.LastName
AS FullName,
 (SELECT tblRankData.Rank
  FROM tblRankData
  WHERE tblRankData.Grade = tblPeople.SupGrade)
 + " " + tblPeople.SupName
AS Supervisor
FROM tblPeople
INNER JOIN (tblParticipants
 INNER JOIN tblDetails
 ON [tblParticipants].[DetailID]=[tblDetails].[DetailID])  
ON [tblPeople].[PeopleID]=[tblParticipants].[PeopleID]
ORDER BY [tblDetails].[DetailDate] DESC;

This works as a query but not as a recordsource for a
report.  I know that the subquery for tblRankData.Rank is
the problem, but what can I do to get this information
without biting this problem?  Can I pull the query into a
temporary table or somewhat and then base the report on
that?

Lon Ingram



Mon, 07 Nov 2005 00:28:34 GMT  
 Multi-Level GROUP BY clause not allowed in a subquery

Quote:

>I get the following error message in a report I am trying
>to construct:

>"Multi-Level GROUP BY clause not allowed in a subquery"

>This is the SQL statement the query is based on:

>SELECT
>tblPeople.*,
>tblDetails.*,
> (SELECT tblRankData.Rank
>  FROM tblRankData
>  WHERE tblRankData.Grade = tblPeople.Grade)
> + " " + tblPeople.FirstName
> + " " +tblPeople.LastName
>AS FullName,
> (SELECT tblRankData.Rank
>  FROM tblRankData
>  WHERE tblRankData.Grade = tblPeople.SupGrade)
> + " " + tblPeople.SupName
>AS Supervisor
>FROM tblPeople
>INNER JOIN (tblParticipants
> INNER JOIN tblDetails
> ON [tblParticipants].[DetailID]=[tblDetails].[DetailID])  
>ON [tblPeople].[PeopleID]=[tblParticipants].[PeopleID]
>ORDER BY [tblDetails].[DetailDate] DESC;

>This works as a query but not as a recordsource for a
>report.  I know that the subquery for tblRankData.Rank is
>the problem, but what can I do to get this information
>without biting this problem?

This happens because Access reports have to munge your
record source query to deal with the Sorting And Grouping
options and aggregate functions(Count, Sum, etc) that you're
using in the report.

Off the top of my head it looks like you can Join the table
tblRankData instead of using the subqueries.

While it is almost always best to retrieve those values in
the query, an alternative is to drop those cloumns from the
query and use DLookup in the controls in the report to
retrieve the rank data.

--
Marsh
MVP [MS Access]



Mon, 07 Nov 2005 01:49:03 GMT  
 Multi-Level GROUP BY clause not allowed in a subquery

Save your sql statement as a query, then create a new
query that selects all the data from your saved query
(Select * from qryMultiLevelGroup).

Use the new query as the recordsource for your report and
you shouldn't have any problem.

hth,
Lance

Quote:
>-----Original Message-----
>I get the following error message in a report I am trying
>to construct:

>"Multi-Level GROUP BY clause not allowed in a subquery"

>This is the SQL statement the query is based on:

>SELECT
>tblPeople.*,
>tblDetails.*,
> (SELECT tblRankData.Rank
>  FROM tblRankData
>  WHERE tblRankData.Grade = tblPeople.Grade)
> + " " + tblPeople.FirstName
> + " " +tblPeople.LastName
>AS FullName,
> (SELECT tblRankData.Rank
>  FROM tblRankData
>  WHERE tblRankData.Grade = tblPeople.SupGrade)
> + " " + tblPeople.SupName
>AS Supervisor
>FROM tblPeople
>INNER JOIN (tblParticipants
> INNER JOIN tblDetails
> ON [tblParticipants].[DetailID]=[tblDetails].
[DetailID])  
>ON [tblPeople].[PeopleID]=[tblParticipants].[PeopleID]
>ORDER BY [tblDetails].[DetailDate] DESC;

>This works as a query but not as a recordsource for a
>report.  I know that the subquery for tblRankData.Rank is
>the problem, but what can I do to get this information
>without biting this problem?  Can I pull the query into a
>temporary table or somewhat and then base the report on
>that?

>Lon Ingram
>.

>-----Original Message-----
>I get the following error message in a report I am trying
>to construct:

>"Multi-Level GROUP BY clause not allowed in a subquery"

>This is the SQL statement the query is based on:

>SELECT
>tblPeople.*,
>tblDetails.*,
> (SELECT tblRankData.Rank
>  FROM tblRankData
>  WHERE tblRankData.Grade = tblPeople.Grade)
> + " " + tblPeople.FirstName
> + " " +tblPeople.LastName
>AS FullName,
> (SELECT tblRankData.Rank
>  FROM tblRankData
>  WHERE tblRankData.Grade = tblPeople.SupGrade)
> + " " + tblPeople.SupName
>AS Supervisor
>FROM tblPeople
>INNER JOIN (tblParticipants
> INNER JOIN tblDetails
> ON [tblParticipants].[DetailID]=[tblDetails].
[DetailID])  
>ON [tblPeople].[PeopleID]=[tblParticipants].[PeopleID]
>ORDER BY [tblDetails].[DetailDate] DESC;

>This works as a query but not as a recordsource for a
>report.  I know that the subquery for tblRankData.Rank is
>the problem, but what can I do to get this information
>without biting this problem?  Can I pull the query into a
>temporary table or somewhat and then base the report on
>that?

>Lon Ingram
>.



Mon, 07 Nov 2005 04:06:01 GMT  
 Multi-Level GROUP BY clause not allowed in a subquery

Quote:
>Off the top of my head it looks like you can Join the
table
>tblRankData instead of using the subqueries.

>While it is almost always best to retrieve those values in
>the query, an alternative is to drop those cloumns from
the
>query and use DLookup in the controls in the report to
>retrieve the rank data.

The problem I had with the join was that there are two
fields (tblPeople.Grade, and tblPeople.SupGrade) that need
to look up tblRankData.Rank.  How can I do the join so
that I get Rank for each of the Grade fields (which will
almost always be different)?

Lon Ingram



Mon, 07 Nov 2005 19:19:18 GMT  
 Multi-Level GROUP BY clause not allowed in a subquery

Quote:

>>Off the top of my head it looks like you can Join the
>>table tblRankData instead of using the subqueries.

>>While it is almost always best to retrieve those values in
>>the query, an alternative is to drop those cloumns from
>>the query and use DLookup in the controls in the report to
>>retrieve the rank data.

>The problem I had with the join was that there are two
>fields (tblPeople.Grade, and tblPeople.SupGrade) that need
>to look up tblRankData.Rank.  How can I do the join so
>that I get Rank for each of the Grade fields (which will
>almost always be different)?

Use two joins?

--
Marsh
MVP [MS Access]



Mon, 07 Nov 2005 22:15:14 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Help: DataEnvironment Multi-level Grouping

2. Help Multi Level Grouping a SINGLE table using DataReport

3. Help: DataEnvironment Multi-level Grouping

4. SQL statement with GROUP BY clause not consistent

5. using recordset(subquery) as target of FROM clause

6. VB6 - Capture KeyUp at Form Level - Not FileListBox Control Level

7. Subquery in Subquery

8. not-match subqueries in VB?

9. Correlated subquery in DELETE stmt not work in Access '97 or 2000

10. GROUP BY clause in Access SQL

11. Bit column in a group by clause

12. SQL - GROUP BY CLAUSE

 

 
Powered by phpBB® Forum Software