query

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

 

[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

 

[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

 

[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!

 

[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!)






.