Solved Sum of one filed based on Yes/No field value

[hrdpgajjar]

Hi all,
I have a table which has following columns,

- Product Item
- Rate
- Is Head unit item (yes no field)
- Quantity
- Amount

Now i need sum of amount where "Is Head Unit item" field has value yes.


I have tried "Sum(IIF([Is Head Unit Item],1,0)) but not able to get the sum value.


How can i do this?


Thanks,


- Hardip

 

[GaP42]

I don't know what "sum" you need - I presume it is a calculation in which Quantity and Amount is involved using Rate, but I have no idea what it is you are trying to calculate.
Do you need this in a query, or show on a form as a control in which the calculation is done?
Could be:

iif([Is Head Unit Item] = -1, Quantity*Rate, 0

Yes/No Field - assuming Boolean type is -1 or 0

 

[CJ_London]

Or

Ttl: -sum([is head unit item]*quantity)

 

[DickyP]

Just do a Dcount

 

[cheekybuddha]

Just do a Dcount

Probably a DSum() would fit better here?

 

[plog]

Now i need sum of amount where "Is Head Unit item" field has value yes.

This will do exactly that:

SELECT SUM(Amount) AS TotalAmount
FROM YourTableNameHere
WHERE [Is Head unit item]

Of course what people say they want and what they actually want are often 2 different things. If the above code doesn't do what you actually want, please tell me how it fails. Better yet, demonstrate what you actually want with data.

 

[LarryE]

DSum("Amount","TABLE NAME","[Is Head unit item]=1")
You are summing the Amount field in your table where the Is Head unit item field is True (1)

 

[isladogs]

As the sum of e.g. 20 true Boolean values = -20, you can either do -Sum (see post #3) or

Total: Abs(sum([is head unit item]*quantity))

 

[DHookom]

It's fairly clear the OP's requirements are vague based on the variety of replies. My guess matches CJ_London and isladogs.

 

[hrdpgajjar]

I don't know what "sum" you need - I presume it is a calculation in which Quantity and Amount is involved using Rate, but I have no idea what it is you are trying to calculate.
Do you need this in a query, or show on a form as a control in which the calculation is done?
Could be:

iif([Is Head Unit Item] = -1, Quantity*Rate, 0

Yes/No Field - assuming Boolean type is -1 or 0

i need sum of "Amount" Field but condition is where "Is Head Unit" value is Yes only.

 

[hrdpgajjar]

As the sum of e.g. 20 true Boolean values = -20, you can either do -Sum (see post #3) or

Total: Abs(sum([is head unit item]*quantity))

This solved my problem. Thanks