Products as Total (Aggregation) 1

[alwayshouston]

Hi All
I have a table that has following records:

Dept Value
ACCT 10
ACCT 20
ACCT 40
MKTG 50
MKTG 10
R&D 20
R&D 5

I need my output to be in group by Dept and instead of SUM I need to use product as my aggregate. In other words, my output should be as follow:

Dept Value
ACCT 8000 <===10*20*40=800
MKTG 500
R&D 100

Thanks in Advance!!!!

 

[dhookom]

Recalling my old slide rule days, you could sum the log and then take the exponent of the sums...

SELECT Dept, Exp(Sum(Log([Value]))) AS Product
FROM tblDepts
GROUP BY Dept;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[alwayshouston]

Thanks Duane,
That works like a charm )