Total=COUNT - COUNT 2

[qwertyth]

Hello, first time here,I hope I am posting to the correct forum.

I have a table with say 10 columns C1,..,C2 with 20 entry's of yes or no each.

I am trying to subtract the COUNT(C2)-COUNT(C1)

I am trying

SELECT PM11, PM21, COUNT(PM21) - Count(PM11) AS Total
FROM tblFlash4
WHERE PM11='Yes' AND PM21='Yes'
GROUP BY PM11, PM21;

I get total = 0 but should be 1.

Clearly doing something wrong.

Thanks

 

[SkipVought]

Hi,

you have destined yourself to a total of ZERO, with...

WHERE PM11='Yes' AND PM21='Yes'

What you might want is...

SELECT COUNT(IIF(PM21='Yes',1,0)) - Count(IIF(PM11='Yes',1,0)) AS Total
FROM tblFlash4

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Counting a yes/no field will count every record that is not null. That's how count works.

If you want to count the number of records where PM21 is yes then use:

 Sum(Abs(PM21))

If your fields are actually text then try:

 Sum(Abs(PM21="Yes"))

I would question the table structure that has "say 10 columns" that are yes/no and have names like PM11 and PM21. It seems un-normalized but I could be wrong.


Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

oops yes SUM not COUNT! Thanx Duane!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Skip,
My post was created before I saw yours and only regarded the OP. Any reference to your reply was just by chance ;-)

Duane
Hook'D on Access
MS Access MVP

 

[qwertyth]

Thank you both for your reply.

They are text fields not yes/no fields, I should have made that clear.

The table is not normalized it came from a survey that some else wrote and then handed off to me.

I have not used the IFF before, I will read up on it, also Sum(abs(PM11="yes")) is new, new to this as well as you can see.

Thanks again.

 

[qwertyth]

P.S

The Thank dhookom link was not working