Yes/No column counts all. 1

[lars7]

Hi,
I have a problem trying to count a Yes/No column in my query. The column is being counted as if all the boxes are ticked but this is not the case. Does anyone know how to sort this.

 

[lespaul]

what's the SQL of the query?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lars7]

Hi Les,
Thanks for replying, the Sql is:

SELECT tblprofiles.Jobfamily, Count(tblassimilation.PayNumber) AS CountOfPayNumber, Count(tblassimilation.KSF) AS CountOfKSF
FROM tblassimilation RIGHT JOIN tblprofiles ON tblassimilation.[Post Descriptor] = tblprofiles.Postdescriptor
GROUP BY tblprofiles.Jobfamily;

at the moment both columns are counting the same although there is only 1 box ticked in the KSF field and the payNumber field should be a count of all the staff.

 

[lespaul]

if you only want to count the field if it's checked then, here's one way:

SUM(iif(tblassimilation.KSF, 1, 0)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lars7]

Thanks Les,
Thats exactly what I was looking for.

SELECT tblprofiles.Jobfamily, Count(tblassimilation.PayNumber) AS StaffCount, Sum(IIf([tblassimilation].[KSF],1,0)) AS KSFCount
FROM tblassimilation RIGHT JOIN tblprofiles ON tblassimilation.[Post Descriptor] = tblprofiles.Postdescriptor
GROUP BY tblprofiles.Jobfamily;