Counting Data In Columns

D

DanielWalters6

I have a database table in which are yes/no fields.

I need to be able to count the number of yes' (ie, -1) in each column.

So for my Alcohol field (I'm working on a database for a health survey)
which is Yes/No field I need to be able to count the number of Yes resoponses.

I need to do this for 15 fields in total.

I then need to create a report of just the top five.

Please help! We've got a deadline to meet, and this is the only thing
holding us up!

TIA

DAN WALTERS

danielwalters(at)mac.com
 
J

John Spencer

It sounds as if it is too late for you to redesign your data base. You will
need to use a Union query to get your data properly organized.

Hopefully, you won't run into a problem with this solution. Since a large
number of queries can cause a union query to fail.


SELECT Count(*) as YesAnswer, "Alcohol" as ItemType
FROM YourTable
WHERE Alcohol = True
UNION
SELECT Count(*) , "Drugs"
FROM YourTable
Where Drugs = True
UNION
....
SELECT COUNT(*), "Smoking"
FROM YourTable
WHERE Smoking = True

Save that as q_Results and then you can use a top 5 query against that.

SELECT TOP 5 ItemType, YesAnswer
FROM q_Results
ORDER BY YesAnswer DESC

You could get all the counts in a vertical row using som,ething like the
following.

SELECT Abs(Sum(Alcohol)) as AlcoholYes
, Abs(Sum(Drugs)) as DrugYes
, ...
, Abs(Sum(Smoking)) as SmokingYes
FROM YourTable
 
Top