ms access product function

[Honestcon]

A simple query - I want to multiply two fields, CategoryPrice and ProductQua,
from my Extras table.
This is the code I wrote:
SELECT EXP(SUM(LOG(CategoryPrice))), ProductQua
FROM Extras

And this is the error I got:
You tried to execute and expressio that does not include the specified
expression 'ProductQua' as par of an aggregate function.

How do I rectify this, please.

 

[vanderghast]

You missed the GROUP BY clause, probably:


SELECT EXP(SUM(LOG(CategoryPrice))), ProductQua
FROM Extras
WHERE categeryPrice >0
GROUP BY ProductQua



Else, what is the role of ProductQua, assuming your table has

CategoryPrice, ProductQua
2 10
3 14


the result would be:

EXP(SUM(LOG(CategoryPrice))), ProductQua
6, ???



??? being what ProductQua will be used? 10? 14?

That is why SQL requires that either ProductQua is in the GROUP BY, either
that you aggregate it (such as MAX(ProductQua) )




Vanderghast, Access MVP

 

[Tom van Stiphout]

On Fri, 19 Jun 2009 05:16:01 -0700, Honestcon

Select CategoryPrice * ProductQua
From Extras

-Tom.
Microsoft Access MVP