Help creating a report to give statistics on data

[RayKinStL]

I have a table that has the following fields....



StuID (primary Key)
Last Name
First Name
Grade Level
Team
Race

Race has 6 choices under it (W, B, A, I, M, H)

I have another table that feeds a survey online that has 11 Y/N questions. The answers populate this table, so that I end up with a table that looks like...

StuID (Primary Key) (linked relationship to data table above)
Q1YN
Q2YN
Q3YN
Q4YN
Q5YN
Q6YN
Q7YN
Q8YN
Q9YN
Q10YN
Q11YN

This table is then filled with checkboxes that are checked if the student selected "Yes" and not checked if they selected "No"

What I want to do is extract a report from this data that will tell me, by race, total users who selected "Yes" and total who selected "No"

So my results would show, for instance...

W
Y-15
N-10

B
Y-4
N-3

H
Y-7
N-12

I
Y-5
N-2

A
Y-7
N-2

M
Y-2
N-1

Now IDEALLY, this data would flow into individual pie charts (one for each race) to make the data easier to read in a visual manner. However, just getting it in the format above would be a huge step. I could easily do this all manually in excel doing some sorting and creating extra worksheets, but I can't figure out how to get this to work with reports and such. Anyone that can put me on the right track would be awesome. To this point, I created a query that game me Sums for each race on each question (when they chose "Yes") although that number was negative (since "Yes" is stored as -1). I have another query that gave me totals, by race, on each question. But I can't find a way to take one field of data from one query, combine it with on field of data from another query, do some subtraction (Total - Yes = No) and then display a chart or percentage comparing the Yes and No answers by Race. Ultimately I will be doing this for other categories including Team (of which there are 9) and Grade Level (of which there are 3). These should all be similar though, so once I solve this issue, I think it should be smooth sailing.

Thanks for reading and any suggestions. If you need more info, do not hesitate to ask!

[Dal Jeanis]

1) It would be easier to get useful information out of your database structure if you normalized the answer table like this

tblStudents
   StuID   PK   
   Race    Text

tblAnswers
   StuID   FK to tblStudents
   QNbr    Number (1-11)
   QYN     bool Y/N

Then, you could get breakdowns for each individual question by race or whatever with something like this

SELECT TS.Race, SUM(IIF(TA.QYN,1,0) AS RaceCountY, SUM(IIF(TA.QYN,0,1) AS RaceCountN
FROM 
   tblStudents AS TS
   INNER JOIN 
   tblAnswers AS TA
   ON TS.StuID = TA.StuID
WHERE QNbr = 1
GROUP BY TS.Race;

2)You can leave off the WHERE clause to get the total of all questions. You can change SUM to AVG to get the average number (ie the percentage) of Yeses and Nos.

3) I don't see sex in your info, so I'm assuming you're dealing with gender-segregated sports of some sort.