J
JK
I'm pulling my hair out over this:
I start out with a query that looks at one table. Two of the fields in
the query are restricted by criteria contained on a form. In addition,
there are a couple calculated fields that perform subtraction on fields
from the table (e.g. [MyTable].[Field1]-[MyTable].[Field2] As
DifferenceofFields). When I run that query with the form open, all is
well. The name of this query is qry1PagersBoth.
Next, I created a query based on qry1PagersBoth in which I do something
very simple (at least in my mind). I want to select certain fields
from qry1Pagers, and Group By one of the fields to get aggregates of
all the other fields selected. The SQL is:
SELECT qry1PagersBoth.Intermediary, Count(qry1PagersBoth.[Grant
Number]) AS [CountOfGrant Number],
Sum(qry1PagersBoth.SmallGrantEstAmount) AS SumOfSmallGrantEstAmount,
Sum(qry1PagersBoth.SmallGrantDisbursed) AS SumOfSmallGrantDisbursed,
Sum(qry1PagersBoth.SmallGrantUndisbursed) AS
SumOfSmallGrantUndisbursed, Sum(qry1PagersBoth.NonSmallGrantEstAmount)
AS SumOfNonSmallGrantEstAmount,
Sum(qry1PagersBoth.NonSmallGrantDisbursed) AS
SumOfNonSmallGrantDisbursed,
Sum(qry1PagersBoth.NonSmallGrantUndisbursed) AS
SumOfNonSmallGrantUndisbursed, Sum(qry1PagersBoth.GrantAmount) AS
SumOfGrantAmount, Sum(qry1PagersBoth.AmountDisbursed) AS
SumOfAmountDisbursed, Sum(qry1PagersBoth.TotalUndisbursed) AS
SumOfTotalUndisbursed
FROM qry1PagersBoth
GROUP BY qry1PagersBoth.Intermediary;
The error I get from doing this is:
"This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables."
If I turn off the Totals (no aggregates) and just display the raw data
(simple select statement), it executes correctly - of course. Once I
try aggregation though - even if ALL of the fields are part of the
Group By clause (i.e. no Count/Sum/etc) - I get the same error message.
I'm thinking that it has something to do with the fact that there is a
mathematical expression in qry1PagersBoth (Field1-Field2), but why
should that matter? A query based on a query is just a query based on
a set of data - right?
Any ideas would be greatly appreciated.
Thanks,
JK
I start out with a query that looks at one table. Two of the fields in
the query are restricted by criteria contained on a form. In addition,
there are a couple calculated fields that perform subtraction on fields
from the table (e.g. [MyTable].[Field1]-[MyTable].[Field2] As
DifferenceofFields). When I run that query with the form open, all is
well. The name of this query is qry1PagersBoth.
Next, I created a query based on qry1PagersBoth in which I do something
very simple (at least in my mind). I want to select certain fields
from qry1Pagers, and Group By one of the fields to get aggregates of
all the other fields selected. The SQL is:
SELECT qry1PagersBoth.Intermediary, Count(qry1PagersBoth.[Grant
Number]) AS [CountOfGrant Number],
Sum(qry1PagersBoth.SmallGrantEstAmount) AS SumOfSmallGrantEstAmount,
Sum(qry1PagersBoth.SmallGrantDisbursed) AS SumOfSmallGrantDisbursed,
Sum(qry1PagersBoth.SmallGrantUndisbursed) AS
SumOfSmallGrantUndisbursed, Sum(qry1PagersBoth.NonSmallGrantEstAmount)
AS SumOfNonSmallGrantEstAmount,
Sum(qry1PagersBoth.NonSmallGrantDisbursed) AS
SumOfNonSmallGrantDisbursed,
Sum(qry1PagersBoth.NonSmallGrantUndisbursed) AS
SumOfNonSmallGrantUndisbursed, Sum(qry1PagersBoth.GrantAmount) AS
SumOfGrantAmount, Sum(qry1PagersBoth.AmountDisbursed) AS
SumOfAmountDisbursed, Sum(qry1PagersBoth.TotalUndisbursed) AS
SumOfTotalUndisbursed
FROM qry1PagersBoth
GROUP BY qry1PagersBoth.Intermediary;
The error I get from doing this is:
"This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables."
If I turn off the Totals (no aggregates) and just display the raw data
(simple select statement), it executes correctly - of course. Once I
try aggregation though - even if ALL of the fields are part of the
Group By clause (i.e. no Count/Sum/etc) - I get the same error message.
I'm thinking that it has something to do with the fact that there is a
mathematical expression in qry1PagersBoth (Field1-Field2), but why
should that matter? A query based on a query is just a query based on
a set of data - right?
Any ideas would be greatly appreciated.
Thanks,
JK