Adding Yeses in Y/N fields

[Guest]

How can I get a value for the total Yeses and a value for the total Nos in a
Y/N field?
Thanks in advance, Shannon

 

[Rick Brandt]

How can I get a value for the total Yeses and a value for the total Nos in a
Y/N field?
Thanks in advance, Shannon

SELECT Abs(Sum(FieldName)) as YesCount,
Sum(FieldName+1) as NoCount
FROM TableName

Yes/True is stored as -1 and No/False as zero.

OR

SELECT FieldName, Count(*) as count
FROM TableName
GROUP BY FieldName

The first will give you two columns with the results while the second will give
you two rows.

 

[Guest]

If it's within one query where you can't add the criteria, you can try
something like

SELECT Sum(IIf([YesNoFieldName]=True,1,0)) AS TrueCunt,
Sum(IIf([YesNoFieldName]=False,1,0)) AS FalseCunt
FROM TableName

 

[Guest]

This really helped. I got just what I wanted. Unfortunately, the office I'm
doing this for just clarified the request. Rather than the total yeses, they
want the total footage associated with each Yes. For instance, with three
records having total footages of 80, 90 and 100 and each with Y, N and Y
consecutively. The office is looking for the percentage of Yes footage, or
((80+100)/(80+90+100))*100. Can you help me?

If it's within one query where you can't add the criteria, you can try
something like

SELECT Sum(IIf([YesNoFieldName]=True,1,0)) AS TrueCunt,
Sum(IIf([YesNoFieldName]=False,1,0)) AS FalseCunt
FROM TableName


--
Good Luck
BS"D

How can I get a value for the total Yeses and a value for the total Nos in a
Y/N field?
Thanks in advance, Shannon

 

[John Spencer]

Sum(IIF(YesNoFieldName=True,FootageField,Null)) as YesFootage

Sum(IIF(YesNoFieldName=True,FootageField,Null))/ Sum(FootageField) as
YesPercent

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This really helped. I got just what I wanted. Unfortunately, the office
I'm
doing this for just clarified the request. Rather than the total yeses,
they
want the total footage associated with each Yes. For instance, with three
records having total footages of 80, 90 and 100 and each with Y, N and Y
consecutively. The office is looking for the percentage of Yes footage,
or
((80+100)/(80+90+100))*100. Can you help me?

If it's within one query where you can't add the criteria, you can try
something like

SELECT Sum(IIf([YesNoFieldName]=True,1,0)) AS TrueCunt,
Sum(IIf([YesNoFieldName]=False,1,0)) AS FalseCunt
FROM TableName


--
Good Luck
BS"D

How can I get a value for the total Yeses and a value for the total Nos
in a
Y/N field?
Thanks in advance, Shannon