sum formula feature

  • Thread starter Thread starter Brenda Rainville
  • Start date Start date
B

Brenda Rainville

I have a formula that starts with sum(format(Iff.....
I am assumint that because it has sum in the front that is
what triggers it to tatal every thing in this field and
give a sum answer.

I am trying to do a report based of the same if function
but do not want it totaled. I want to use the same iff
funtion that is set up with this sum(fomat in front but
how do I get rid of the sum feature. I have tried to
delete sum(format( and then another closing ) at the end
but it is coming back with error messages.

Any insight would be great.

thanks,
Brenda
 
Hi,


You are right to say that SUM( any_expression ) will make a sum, for
the expression, compute for each and every record, a vertical sum if you
prefer, for each GROUP BY you may have (or for the whole table, if you do
not specify any group).

Removing the three letters SUM, the opening ( and its corresponding )
would still leave you with a valid expression, but not allowed in a total
(group by) query, in the SELECT clause. Indeed, for a total (group by)
query, every "selected" thing should be EITHER mentioned in the GROUP BY,
either aggregated (through a SUM, COUNT, MIN, MAX, LAST, FIRST, ...). So,
as example, if you have:


Country Amount
US 10
US 20
US 30


Then, what would be the result of:


SELECT Country, Amount
FROM tableAbove
GROUP BY Country



because, indeed, if we group by Country, US should be returned just once in
the result... but what "amount" will we associate with it? If you want
any, use Last, or First, as aggregate:

SELECT Country, LAST(Amount)
FROM tableAbove
GROUP BY Country


(note that this does not necessary return Amount = 30 )


If you want each amount, then, place it in the GROUP BY:

SELECT Country, Amount
FROM tableAbove
GROUP BY Country, Amount


but definitively, if Amount is not in the group by, it MUST be aggregated
(with SUM, COUNT, MIN, MAX, LAST, FIRST, .... ) or be an expression
involving constants, aggregated, or expressions present in the GROUP BY,
like


SELECT Country,
"" >>>" & Country & "<<<" As ValidExpression,
SUM(amount) / MAX(amount) As AnotherValidExpression
FROM tableAbove
GROUP BY Country






Hoping it may help,
Vanderghast, Access MVP
 
Back
Top