By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,651 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,651 IT Pros & Developers. It's quick & easy.

aggregate function error

P: n/a
I have a query where I am grouping by Fiscal Month, Summing # of Quotes,
Summing # of Days.......and created an expression.........Avg Days: [# of
Quotes]/[Days Open].

And I get the following error message.........anyone know why?

"You tried to execute a query that does not include the specified expression
"[# of Quotes]/[Days Open]" as part of an aggregate function.

Both # of Quotes and Days are numerical fields........why can't I divide one
into the other?

Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 21 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Lori2836 via AccessMonster.com wrote:
I have a query where I am grouping by Fiscal Month, Summing # of Quotes,
Summing # of Days.......and created an expression.........Avg Days: [# of
Quotes]/[Days Open].

And I get the following error message.........anyone know why?

"You tried to execute a query that does not include the specified expression
"[# of Quotes]/[Days Open]" as part of an aggregate function.

Both # of Quotes and Days are numerical fields........why can't I divide one
into the other?

Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1
Go to SQL view and add [# of Quotes]/[Days Open] to the GROUP BY list:

GROUP BY [Fiscal Month], ..., [# of Quotes]/[Days Open]

James A. Fortune
CD********@FortuneJames.com

Dec 21 '06 #2

P: n/a
Thanks James. I must still be doing something wrong. Still getting an error
message.
Here is my SQL:

SELECT [Sum for Monthly Chart].[Fiscal Month], Sum([Sum for Monthly Chart].[#
of Quotes]) AS [SumOf# of Quotes], Sum([Sum for Monthly Chart].[Days Open])
AS [Days Open], [# of Quotes]/[Days Open] AS [Avg Days]
FROM [Sum for Monthly Chart]
GROUP BY [Sum for Monthly Chart].[Fiscal Month], [# of Quotes]/[Days Open];
CD********@FortuneJames.com wrote:
>I have a query where I am grouping by Fiscal Month, Summing # of Quotes,
Summing # of Days.......and created an expression.........Avg Days: [# of
[quoted text clipped - 13 lines]
>Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Go to SQL view and add [# of Quotes]/[Days Open] to the GROUP BY list:

GROUP BY [Fiscal Month], ..., [# of Quotes]/[Days Open]

James A. Fortune
CD********@FortuneJames.com
--
Message posted via http://www.accessmonster.com

Dec 22 '06 #3

P: n/a
Lori2836 via AccessMonster.com wrote:
Thanks James. I must still be doing something wrong. Still getting an error
message.
Here is my SQL:

SELECT [Sum for Monthly Chart].[Fiscal Month], Sum([Sum for Monthly Chart].[#
of Quotes]) AS [SumOf# of Quotes], Sum([Sum for Monthly Chart].[Days Open])
AS [Days Open], [# of Quotes]/[Days Open] AS [Avg Days]
FROM [Sum for Monthly Chart]
GROUP BY [Sum for Monthly Chart].[Fiscal Month], [# of Quotes]/[Days Open];
If you are you trying to divide by [Days Open] rather than the Sum of
[Days Open] try:

SELECT [Fiscal Month], Sum([# of Quotes]) AS [SumOf# of Quotes],
Sum([Days Open]) AS [Sum of Days Open], [# of Quotes]/[Days Open] AS
[Avg Days] FROM [Sum for Monthly Chart] GROUP BY [Fiscal Month], [#
of Quotes]/[Days Open];

If you are trying to divide by Sum of [Days Open] you'll probably be
better off using two queries. Let me know if that is the case and I'll
see what I can come up with.

James A. Fortune
CD********@FortuneJames.com

Dec 22 '06 #4

P: n/a
Got it James........thank you! And have a wonderful holiday!

CD********@FortuneJames.com wrote:
>Thanks James. I must still be doing something wrong. Still getting an error
message.
[quoted text clipped - 5 lines]
>FROM [Sum for Monthly Chart]
GROUP BY [Sum for Monthly Chart].[Fiscal Month], [# of Quotes]/[Days Open];

If you are you trying to divide by [Days Open] rather than the Sum of
[Days Open] try:

SELECT [Fiscal Month], Sum([# of Quotes]) AS [SumOf# of Quotes],
Sum([Days Open]) AS [Sum of Days Open], [# of Quotes]/[Days Open] AS
[Avg Days] FROM [Sum for Monthly Chart] GROUP BY [Fiscal Month], [#
of Quotes]/[Days Open];

If you are trying to divide by Sum of [Days Open] you'll probably be
better off using two queries. Let me know if that is the case and I'll
see what I can come up with.

James A. Fortune
CD********@FortuneJames.com
--
Message posted via http://www.accessmonster.com

Dec 22 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.