query

U

understorm

In one of my tables, I had field which which allow users
to select Yes or No. IS there a way I can query the number
of Yes or No?
 
D

Douglas J. Steele

Two approaches.

You can have queries such as the following:

SELECT Count(*) AS NumberTrue
FROM MyTable
WHERE MyField = True

SELECT Count(*) AS NumberFalse
FROM MyTable
WHERE MyField = False

or you can rely on the fact that True is stored as -1 and use

SELECT Abs(Sum(MyField)) AS NumberTrue
FROM MyTable
 
D

Damon Heron

You can query both with:
SELECT DISTINCTROW Sum(ABS(YourTable.YNField)) AS Yeses, Count(YourTable.*)
AS CountOfAll, (CountOfAll-Yeses) AS Nos
FROM YourTable;

Damon
 
U

understorm

Thanks for the help, if i wish to followed up to further
list the departments and sections which the users select
yes or no. Is there a way other than using lookup wizard
which limits to 20 columns. I need 20++ columns for my
departments and sections!
 
U

understorm

Thanks.
-----Original Message-----
Two approaches.

You can have queries such as the following:

SELECT Count(*) AS NumberTrue
FROM MyTable
WHERE MyField = True

SELECT Count(*) AS NumberFalse
FROM MyTable
WHERE MyField = False

or you can rely on the fact that True is stored as -1 and use

SELECT Abs(Sum(MyField)) AS NumberTrue
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)






.
Click to expand...
 
Top