Counting Data In Columns

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

Guest

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
 
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
 
Back
Top