Multi-level GROUP BY clause not allowed in a subquery

[Steve S]

I have a table (tblRecapB) with 40 fields. A Query qryRecap of this table
uses a variation of the following subquery to rank [MPP] into [MRank], [SPP]
into [SRank] and [TPP] into [TRank]. xPP stands for place points (something
like a score).

MRank: 1+(SELECT Count(*)
FROM tblRecapB AS M
WHERE M.[Fee Id]=tblRecapB.[Fee ID] and M.[AGSortKey]=tblRecapB.[AGSortKey]
And M.MPP < tblRecapB.MPP)

This works great as long as I just run the query standalone.

The problem is that when I use qryRecap as the record source for the final
report I get the following error: “Multi-level GROUP BY clause not allowed
in a subqueryâ€. If I remove the three fields MRank, SRank, and TRank, the
report (all other 40 fields) is OK. If I remove all Group Headers the three
fields generated by the subquery are displayed as expected.

I tried to get around this by creating an intervening query, qryRecapX, that
just selects all 43 fields of qryRecap. I then used this new query as record
source for the report and did not get any errors. All fields and group
headers are displayed. The problem now is that [Mranl], [Srank], and [TRank]
all have a value of 1 but when I run the new query standalone the values for
these 3 fields display correctly.

Next I changed the design of the table tblrecapB by adding the three new
fields to it and trying to use variations of the subquery above in an Update
Query to calculate the ranks. This way the values I need would be
‘hardcoded’ in the table and not calculated at report time. Can’t seem to
get this to work. Should I be using Dcount() instead of Count()???


Any suggestions and/or help is appreciated

 

[Marshall Barton]

I have a table (tblRecapB) with 40 fields. A Query qryRecap of this table
uses a variation of the following subquery to rank [MPP] into [MRank], [SPP]
into [SRank] and [TPP] into [TRank]. xPP stands for place points (something
like a score).

MRank: 1+(SELECT Count(*)
FROM tblRecapB AS M
WHERE M.[Fee Id]=tblRecapB.[Fee ID] and M.[AGSortKey]=tblRecapB.[AGSortKey]
And M.MPP < tblRecapB.MPP)

This works great as long as I just run the query standalone.

The problem is that when I use qryRecap as the record source for the final
report I get the following error: “Multi-level GROUP BY clause not allowed
in a subquery”. If I remove the three fields MRank, SRank, and TRank, the
report (all other 40 fields) is OK. If I remove all Group Headers the three
fields generated by the subquery are displayed as expected.

I tried to get around this by creating an intervening query, qryRecapX, that
just selects all 43 fields of qryRecap. I then used this new query as record
source for the report and did not get any errors. All fields and group
headers are displayed. The problem now is that [Mranl], [Srank], and [TRank]
all have a value of 1 but when I run the new query standalone the values for
these 3 fields display correctly.

Next I changed the design of the table tblrecapB by adding the three new
fields to it and trying to use variations of the subquery above in an Update
Query to calculate the ranks. This way the values I need would be
‘hardcoded’ in the table and not calculated at report time. Can’t seem to
get this to work. Should I be using Dcount() instead of Count()???

I suspect there's a little more to it than just the
subqueries, but your idea of using DCount instead of a
subquery should get around the issue:

MRank: 1+DCount("*", "tblRecapB", "[Fee Id] = " &
tblRecapB.[Fee ID] & " And AGSortKey = " &
tblRecapB.AGSortKey] " And MPP < " & tblRecapB.MPP)

 

[Dale Fye]

If you are just trying to use these fields for reporting purposes, you might
want to consider just putting a value of 1 in each of these fields, then in
the report, you can set the "RunningSum" property of the field to Yes.

HTH
Dale

 

[Steve S]

thanks for the suggestion. The report records are sorted by last name so the
running sum won't work in this case.
--
Steve S

If you are just trying to use these fields for reporting purposes, you might
want to consider just putting a value of 1 in each of these fields, then in
the report, you can set the "RunningSum" property of the field to Yes.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table (tblRecapB) with 40 fields. A Query qryRecap of this table
uses a variation of the following subquery to rank [MPP] into [MRank], [SPP]
into [SRank] and [TPP] into [TRank]. xPP stands for place points (something
like a score).

MRank: 1+(SELECT Count(*)
FROM tblRecapB AS M
WHERE M.[Fee Id]=tblRecapB.[Fee ID] and M.[AGSortKey]=tblRecapB.[AGSortKey]
And M.MPP < tblRecapB.MPP)

This works great as long as I just run the query standalone.

The problem is that when I use qryRecap as the record source for the final
report I get the following error: “Multi-level GROUP BY clause not allowed
in a subqueryâ€. If I remove the three fields MRank, SRank, and TRank, the
report (all other 40 fields) is OK. If I remove all Group Headers the three
fields generated by the subquery are displayed as expected.

I tried to get around this by creating an intervening query, qryRecapX, that
just selects all 43 fields of qryRecap. I then used this new query as record
source for the report and did not get any errors. All fields and group
headers are displayed. The problem now is that [Mranl], [Srank], and [TRank]
all have a value of 1 but when I run the new query standalone the values for
these 3 fields display correctly.

Next I changed the design of the table tblrecapB by adding the three new
fields to it and trying to use variations of the subquery above in an Update
Query to calculate the ranks. This way the values I need would be
‘hardcoded’ in the table and not calculated at report time. Can’t seem to
get this to work. Should I be using Dcount() instead of Count()???


Any suggestions and/or help is appreciated