Adding Yeses in Y/N fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Shannon said:
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
Click to expand...

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.
 
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
 
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?

Ofer Cohen said:
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


Shannon said:
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
Click to expand...
Click to expand...
 
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
..

Shannon said:
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?

Ofer Cohen said:
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


Shannon said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top