Help with calculations

[chosenonee]

Hello, I'm using this to calculate totals in an Access 10 db query:



Sum(IIf([sex]="MALE" And [Diag]="200",1,0))+Sum(IIf([DiagII]="200",1,0))+Sum(IIf([DiagIII]="200",1,0))+Sum(IIf([DiagIV]="200",1,0))+Sum(IIf([DiagV]="200",1,0))

That's a copy of one of many similar but it's adding an additional number to the total and I can't figure out what is wrong with it>

Thanks for the help.

Don

[pbaldy]

Hard to tell without seeing the data. You realize that the "Male" test is only applied to the Diag test? Also, your design is suspect with the numbered fields. It likely isn't normalized:

Fundamentals of Relational Database Design -- r937.com

[CJ_London]

you could try

Sum(ABS([sex]="MALE" And [Diag]="200")+ABS([DiagII]="200")+ABS([DiagIII]="200")+ABS([DiagIV]="200")+ABS([DiagV]="200"))

Also - I presume your diag fields are all text and not numeric?

[orange]

In addition to what Paul and Ajax posted, could you tell us in plain English WHAT you are trying to do?

[chosenonee]

Thank you all for chiming in. the purpose is to get a total of the number of "Males" with a 200 diagnosis. I have the same for Females. The "Diag" tables 1-5 are text and they are combo box (Value Lists). Not sure if that makes a difference. I looked briefly at the Relational Database Design from Paul and I see some things I did wrong in creating this massive database but I need a quick fix now as reports are due and we are depending on the database for the information. I tried the suggestion from Ajax, thank you but that didn't work at all. I'd appreciate any other ideas.

[pbaldy]

Each of your tests has to include the "Male" test, or perhaps:

Sum(IIf([sex]="MALE" And ([Diag]="200" OR [DiagII]="200" OR [DiagIII]="200" OR [DiagIV]="200" OR [DiagV]="200"),1,0))

[chosenonee]

Thanks pbaldy, that fixed my problem. Thank you all tremendously. I'm sure I'll be back with more questions and looking to learn more.

[pbaldy]

Happy to help!