errroR

[George Schneider]

i get a multilevel group by clause is not allowed in subquery when i try to place a field in the report footer. What does this mean

 

[Duane Hookom]

Apparently your report's record source contains a subquery and you are
possibly attempting to sort/group by the result of the subquery. This isn't
allowed in Access reports. If you post the SQL and sorting, someone might be
able to identify the specific issue and solution.

--
Duane Hookom
MS Access MVP
--

i get a multilevel group by clause is not allowed in subquery when i try

to place a field in the report footer. What does this mean

 

[George Schneider]

Here's the SQL. The query is based on another query which is based on another quer

SELECT [Total Expenses1].[Expense Name], IIf([Total Expenses1].[Expense Name]="Administrative",Sum([Total Expenses1].[Total Expense])-(SELECT SUM([Total Expenses1].[Total Expense]) FROM [Total Expenses1] WHERE [Total Expenses1].[Expense Name] IN ("Deferred Comp", "Intrest")),Sum([Total Expenses1].[Total Expense])) AS [Total Expense], IIf([Total Expenses1].[Expense Name]="Administrative",Sum([Total Expenses1].[Budget])-(SELECT SUM([Total Expenses1].[Budget]) FROM [Total Expenses1] WHERE [Total Expenses1].[Expense Name] IN ("Deferred Comp", "Intrest")),Sum([Total Expenses1].[Budget])) AS Budge
FROM [Total Expenses1
GROUP BY [Total Expenses1].[Expense Name]

 

[Duane Hookom]

You would possibly need to replace the subqueries with domain aggregate
functions (yuk!). For instance, you could replace the first subquery with
DSum("[Total Expense]","[Total Expenses1]","[Expense Name] IN ('Deferred
Comp','Intrest')")
Since all of your queries and subqueries are based on the same wource, there
may be a more efficient method.
--
Duane Hookom
MS Access MVP
--

Here's the SQL. The query is based on another query which is based on another quer.

SELECT [Total Expenses1].[Expense Name], IIf([Total Expenses1].[Expense

Name]="Administrative",Sum([Total Expenses1].[Total Expense])-(SELECT
SUM([Total Expenses1].[Total Expense]) FROM [Total Expenses1] WHERE [Total
Expenses1].[Expense Name] IN ("Deferred Comp", "Intrest")),Sum([Total
Expenses1].[Total Expense])) AS [Total Expense], IIf([Total
Expenses1].[Expense Name]="Administrative",Sum([Total
Expenses1].[Budget])-(SELECT SUM([Total Expenses1].[Budget]) FROM [Total
Expenses1] WHERE [Total Expenses1].[Expense Name] IN ("Deferred Comp",
"Intrest")),Sum([Total Expenses1].[Budget])) AS Budget

FROM [Total Expenses1]
GROUP BY [Total Expenses1].[Expense Name];