Multi-Level Group By clause not allowed in subquery

[Sara]

I have a query (below) that runs just fine as a query, but
when I try to run the report, I get this message:
Multi-Level Group By clause not allowed in subquery

I use one of the two queries that make up the query for
the report in another report, and I am trying to reuse the
query, but I get this message when I run the report. I
think it has to do with totalling sales by Dept#, but I
don't know how to fix it. I carry a record for the total
(Chain) dept sales, but I am trying to have the report
total the sales so the Chain record isn't considered a
store, and ranked #1 in sales each week.

How can this be fixed?

Q: Merchant Sales Ranked Dept w Store Rank
SELECT [Q: Merchant Dept Sales Desc Order].SalesDate,
[Q: Merchant Dept Sales Desc Order].Merchant,
[Q: Merchant Dept Sales Desc Order].StoreNum,
[Q: Merchant Dept Sales Desc Order].StoreName,
[Q: Merchant Dept Sales Desc Order].[Dept#],
[Q: Merchant Dept Sales Desc Order].DeptName,
[Q: Merchant Dept Sales Desc Order].[WeekTY Sales],
[Q: Merchant Dept Sales Desc Order].YrToDtSales,

(SELECT COUNT(*)
FROM [Q: For Ranking Store Sales] AS T
WHERE T.[YTDTY Sales] >=
[Q: For Ranking Store Sales].[YTDTY Sales])
AS StoreRANK

FROM
[Q: Merchant Dept Sales Desc Order]
INNER JOIN [Q: For Ranking Store Sales] ON
([Q: Merchant Dept Sales Desc Order].StoreNum =
[Q: For Ranking Store Sales].StoreNum)
AND
([Q: Merchant Dept Sales Desc Order].SalesDate =
[Q: For Ranking Store Sales].SalesDate)

WHERE ((([Q: Merchant Dept Sales Desc Order].SalesDate)=
[Enter Week Ending Date]))

ORDER BY
[Q: Merchant Dept Sales Desc Order].Merchant,
[Q: Merchant Dept Sales Desc Order].YrToDtSales DESC;


Q: For Ranking Store Sales:
SELECT [T:Store Sales Data].SalesDate,
[T:Store Sales Data].StoreNum,
[T:Store Sales Data].[YTDTY Sales]
FROM [T:Store Sales Data]

WHERE ((([T:Store Sales Data].StoreNum)<>99))
ORDER BY [T:Store Sales Data].[YTDTY Sales] DESC;

(99 is the "chain" level record)

I have a report that has weekly sales by department and
group of departments and the users would like the YTDRank
on the report, so I thought I'd reuse the same query (Q:
For Ranking Store Sales), as the table with Dept data
doesn't have store totals, and the ranking is by the
entire store.

Hope this makes sense. It's been tough on me for over 2
weks now, and I just know it can be accomplished.
Thanks
Sara

 

[Duane Hookom]

Your subquery is causing the issue. One solution uses the very slow DCount()
function:
DCount("*","[Q: For Ranking Store Sales]","[YTDTY Sales] >=" & [YTDTY
Sales] ) AS StoreRANK

 

[sara]

I tried replacing the "Count" function

(SELECT COUNT(*) FROM [Q: For Ranking Store Sales] AS T
WHERE T.[YTDTY Sales] >= [Q: For Ranking Store Sales].
[YTDTY Sales]) AS StoreRANK

with your DCOUNT code. First I got "YTDTY Sales could
refer to more than one table in your query", so I tried
Help and tried to Qualify the field with the statement
below.

DCount("*","[Q: For Ranking Store Sales]", [YTDTY Sales]

= "& [Q: For Ranking Store Sales]", [YTDTY Sales] ) AS

StoreRANK


Now I get "Wrong number of arguments used with function in
query expression DCOUNT...."

Can you help here? Is there any other (or better)
option, like setting up a table that holds the sales and
ranking daily (or weekly) and just pulling in the rank
field when it's needed?

Thanks.

-----Original Message-----
Your subquery is causing the issue. One solution uses the very slow DCount()
function:
DCount("*","[Q: For Ranking Store Sales]","[YTDTY Sales]
=" & [YTDTY
Sales] ) AS StoreRANK

--
Duane Hookom
MS Access MVP

I have a query (below) that runs just fine as a query, but
when I try to run the report, I get this message:
Multi-Level Group By clause not allowed in subquery

I use one of the two queries that make up the query for
the report in another report, and I am trying to reuse the
query, but I get this message when I run the report. I
think it has to do with totalling sales by Dept#, but I
don't know how to fix it. I carry a record for the total
(Chain) dept sales, but I am trying to have the report
total the sales so the Chain record isn't considered a
store, and ranked #1 in sales each week.

How can this be fixed?

Q: Merchant Sales Ranked Dept w Store Rank
SELECT [Q: Merchant Dept Sales Desc Order].SalesDate,
[Q: Merchant Dept Sales Desc Order].Merchant,
[Q: Merchant Dept Sales Desc Order].StoreNum,
[Q: Merchant Dept Sales Desc Order].StoreName,
[Q: Merchant Dept Sales Desc Order].[Dept#],
[Q: Merchant Dept Sales Desc Order].DeptName,
[Q: Merchant Dept Sales Desc Order].[WeekTY Sales],
[Q: Merchant Dept Sales Desc Order].YrToDtSales,

(SELECT COUNT(*)
FROM [Q: For Ranking Store Sales] AS T
WHERE T.[YTDTY Sales] >=
[Q: For Ranking Store Sales].[YTDTY Sales])
AS StoreRANK

FROM
[Q: Merchant Dept Sales Desc Order]
INNER JOIN [Q: For Ranking Store Sales] ON
([Q: Merchant Dept Sales Desc Order].StoreNum =
[Q: For Ranking Store Sales].StoreNum)
AND
([Q: Merchant Dept Sales Desc Order].SalesDate =
[Q: For Ranking Store Sales].SalesDate)

WHERE ((([Q: Merchant Dept Sales Desc Order].SalesDate)=
[Enter Week Ending Date]))

ORDER BY
[Q: Merchant Dept Sales Desc Order].Merchant,
[Q: Merchant Dept Sales Desc Order].YrToDtSales DESC;


Q: For Ranking Store Sales:
SELECT [T:Store Sales Data].SalesDate,
[T:Store Sales Data].StoreNum,
[T:Store Sales Data].[YTDTY Sales]
FROM [T:Store Sales Data]

WHERE ((([T:Store Sales Data].StoreNum)<>99))
ORDER BY [T:Store Sales Data].[YTDTY Sales] DESC;

(99 is the "chain" level record)

I have a report that has weekly sales by department and
group of departments and the users would like the YTDRank
on the report, so I thought I'd reuse the same query (Q:
For Ranking Store Sales), as the table with Dept data
doesn't have store totals, and the ranking is by the
entire store.

Hope this makes sense. It's been tough on me for over 2
weks now, and I just know it can be accomplished.
Thanks
Sara

.

 

[Duane Hookom]

I never have two fields with the same name so I miss this at times. Also, I
was doing my own little protest against object names with spaces and
punctuation ;-).
Try:
DCount("*","[Q: For Ranking Store Sales]","[YTDTY Sales] >=" & [Q: For
Ranking Store Sales].[YTDTY Sales] ) AS StoreRANK

--
Duane Hookom
MS Access MVP

I tried replacing the "Count" function

(SELECT COUNT(*) FROM [Q: For Ranking Store Sales] AS T
WHERE T.[YTDTY Sales] >= [Q: For Ranking Store Sales].
[YTDTY Sales]) AS StoreRANK

with your DCOUNT code. First I got "YTDTY Sales could
refer to more than one table in your query", so I tried
Help and tried to Qualify the field with the statement
below.

DCount("*","[Q: For Ranking Store Sales]", [YTDTY Sales]

= "& [Q: For Ranking Store Sales]", [YTDTY Sales] ) AS

StoreRANK


Now I get "Wrong number of arguments used with function in
query expression DCOUNT...."

Can you help here? Is there any other (or better)
option, like setting up a table that holds the sales and
ranking daily (or weekly) and just pulling in the rank
field when it's needed?

Thanks.

-----Original Message-----
Your subquery is causing the issue. One solution uses the very slow DCount()
function:
DCount("*","[Q: For Ranking Store Sales]","[YTDTY Sales]
=" & [YTDTY
Sales] ) AS StoreRANK

--
Duane Hookom
MS Access MVP

I have a query (below) that runs just fine as a query, but
when I try to run the report, I get this message:
Multi-Level Group By clause not allowed in subquery

I use one of the two queries that make up the query for
the report in another report, and I am trying to reuse the
query, but I get this message when I run the report. I
think it has to do with totalling sales by Dept#, but I
don't know how to fix it. I carry a record for the total
(Chain) dept sales, but I am trying to have the report
total the sales so the Chain record isn't considered a
store, and ranked #1 in sales each week.

How can this be fixed?

Q: Merchant Sales Ranked Dept w Store Rank
SELECT [Q: Merchant Dept Sales Desc Order].SalesDate,
[Q: Merchant Dept Sales Desc Order].Merchant,
[Q: Merchant Dept Sales Desc Order].StoreNum,
[Q: Merchant Dept Sales Desc Order].StoreName,
[Q: Merchant Dept Sales Desc Order].[Dept#],
[Q: Merchant Dept Sales Desc Order].DeptName,
[Q: Merchant Dept Sales Desc Order].[WeekTY Sales],
[Q: Merchant Dept Sales Desc Order].YrToDtSales,

(SELECT COUNT(*)
FROM [Q: For Ranking Store Sales] AS T
WHERE T.[YTDTY Sales] >=
[Q: For Ranking Store Sales].[YTDTY Sales])
AS StoreRANK

FROM
[Q: Merchant Dept Sales Desc Order]
INNER JOIN [Q: For Ranking Store Sales] ON
([Q: Merchant Dept Sales Desc Order].StoreNum =
[Q: For Ranking Store Sales].StoreNum)
AND
([Q: Merchant Dept Sales Desc Order].SalesDate =
[Q: For Ranking Store Sales].SalesDate)

WHERE ((([Q: Merchant Dept Sales Desc Order].SalesDate)=
[Enter Week Ending Date]))

ORDER BY
[Q: Merchant Dept Sales Desc Order].Merchant,
[Q: Merchant Dept Sales Desc Order].YrToDtSales DESC;


Q: For Ranking Store Sales:
SELECT [T:Store Sales Data].SalesDate,
[T:Store Sales Data].StoreNum,
[T:Store Sales Data].[YTDTY Sales]
FROM [T:Store Sales Data]

WHERE ((([T:Store Sales Data].StoreNum)<>99))
ORDER BY [T:Store Sales Data].[YTDTY Sales] DESC;

(99 is the "chain" level record)

I have a report that has weekly sales by department and
group of departments and the users would like the YTDRank
on the report, so I thought I'd reuse the same query (Q:
For Ranking Store Sales), as the table with Dept data
doesn't have store totals, and the ranking is by the
entire store.

Hope this makes sense. It's been tough on me for over 2
weks now, and I just know it can be accomplished.
Thanks
Sara

.