Summation with a condition. How?

[lactbc]

Suppose I have the following table where Type,1stPOS,2ndPOS,3rdPOS,4thPOS are the fields:

Type 1stPOS 2ndPOS 3rdPOS 4thPOS
Red 1 1 1 1
Blue 2 2 2 2
Blue 2 2 2 2

And I'm trying to place a total field in a new Report.
I got the following to work:

=Sum([1stPOS])+Sum([2ndPOS])+Sum([3rdPOS])+Sum([4thPOS])

This gives me an answer of 20 which is what I want.

Now I want to extract the total with only the Type="Blue" to give me an answer of 16 but I can't figure it out. I was trying things like:

=Sum([1stPOS])+Sum([2ndPOS])+Sum([3rdPOS])+Sum([4thPOS]) And [Type]="Blue"

But it didn't work.

If you happen to have the time. Would you know the solution? Thanks.

 

[dhookom]

Summing across fields generally suggests un-normalized data. However, you should be able to use
=Abs(Sum(([1stPOS]+[2ndPOS]+[3rdPOS]+[4thPOS])*( [Type]="Blue&quot))

Duane
MS Access MVP

 

[lactbc]

Thanks for the suggestion. I tried as you said but I get the following error 2E+12

 

[lactbc]

I should add that some of the numbers are zeros or the field could be blank from 1stPOS-4thPOS.

 

[dhookom]

If your fields might be null then you need to use
(Nz([1stPOS],0)+Nz([2ndPOS],0)+...
This is the penalty you pay for un-normalized data. Make sure your text box is wide enough to display the total and set the format property to Standard.

Duane
MS Access MVP

 

[lactbc]

Again. Thanks for your help but I still can't get it to run correctly. Would you mind if I send you my Access file so you can see what I'm doing? Email me at cfan@dhs.co.la.ca.us

 

[dhookom]

Try:
=Abs(Sum((Nz([1stPOS],0)+Nz([2ndPOS],0)+Nz([3rdPOS],0)+Nz([4thPOS],0))*( [Type]="Blue&quot))

If it doesn't work, then you must tell us what there error message or return value displays.

Duane
MS Access MVP