count & sum

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

Guest

Can you help me with the best way to count some data, all from one table.
Q1 Q2 Q3 Q4 Q5
Bob Y N Y Y D
Bob N N Y D N
Joe N Y Y D Y

Bob had (Q1=1/y and 1/n) (Q2=2/n) (Q3=2/y........
Joe had (Q1=1/n) (Q2=1/n)...........

Is there an SQL code I can put in a query to give me this, or do I need to
have multiable queries 1st to count, then to sum etc...???
 
Dan,
This would be a lot easier if your table were normalized. I'll assume these
are students, and that these responses are to a test. Same technique would
apply if this were a survey. Then I would normalize the table something
like:

StudentID TestID QuestionID Response
Bob 1 1 Y
Bob 1 2 N
Bob 1 3 Y
....
Bob 1 1 N
Bob 1 2 N
....
Joe 1 1 N
Joe 1 2 Y
Joe 1 3 N

Then you could create a crosstab query with StudentID, TestID, and
QuestionID as your row headings, Response as your column heading, and then
use Count(StudentID) as the value for your crosstab.

You did not indicate whether you had a test number to go with this data, so
you might want to through that out, or you might want to put a testDate in
there instead.

You can transform your table into this format with a query that looks like:

SELECT StudentID, 1 as TestID, 1 as QuestionID, Q1 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 2 as QuestionID, Q2 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 3 as QuestionID, Q3 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 4 as QuestionID, Q4 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 5 as QuestionID, Q5 as Response
FROM yourTable

Save this query, then use it as the source of a maketable query to get your
data into a normalized table.

HTH
Dale
 
Can you help me with the best way to count some data, all from one table.
Q1 Q2 Q3 Q4 Q5
Bob Y N Y Y D
Bob N N Y D N
Joe N Y Y D Y

Bob had (Q1=1/y and 1/n) (Q2=2/n) (Q3=2/y........
Joe had (Q1=1/n) (Q2=1/n)...........

Is there an SQL code I can put in a query to give me this, or do I need to
have multiable queries 1st to count, then to sum etc...???
Click to expand...

A better table design would be

Bob, Q1, N
Bob, Q2, N
Bob, Q3, Y
Bob, Q4, Y
Bob, Q5, D
....
Joe, Q4, D
Joe, Q5, Y

But with your current structure, you'll need three calculated fields
in a totals query:

SELECT TheName, Abs(([Q1] = "y") + ([Q2] = "y") + ([Q3] = "y") + ([Q4]
= "y") + ([Q5] = "y")) AS CountOfY

<with two similar expressions for N and D>

You could then create a Totals query grouping by name and summing
CountOfY, CountOfD and CountOfN.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
This is a Physician survey that took place over all of 2004, I cannot change
the table. It's simple Y - N - D (yes/no/does not apply) answers.
There are 5 records per Physician.

My goal is a report, I'll use a macro to open it by entering the physician
number, but I need the Query to gather the records per Physician # and give
totals for questions ie: Physician #1 - question 1=25 yes and 10 no /
question 2........

I then have to add a formula for physicians that answered >80% Yes...

Help....
 
Back
Top