averaging checkboxes

[Sabik]

I teach at a school and am looking for a way to keep track of the scores my
students get on several tests. Additionally I want to know which questions
they miss so that I can analyze how I teach the same subjects for the next
year. (i.e. improve my teaching in areas where lots of students miss the
same question).

Keeping track of the scores in Access is easy, no problems there. My
problem comes when I attempt to keep track of which quesitons are missed. At
the moment I use a checkbox to indicate that a student has missed a given
question.

For Example:
Question1 Question2
StudentID1 X
StudentID2 X X
Totals 2 1

OK, I can get the above results which is great, but what I really want is a
percentage. Such as Question1 represents 66% of the missed questions, and
Question2 represents 33%. I cannot get Access to sum the totals (for a
divisor to obtain the percentage) nor can I get the avg function to do what I
want.

For Example:
Question1 Question2
StudentID1 X
StudentID2 X X
2 1
3 Missed Questions Total
Totals 2/3=66% 1/3=33%

I know how to do this in Excel, but I want some of Access' other
capabilities (the ability to keep track of several different tests and many
students every year), which Excel just doesn't do well. Can Access do this
or am I forced to use Excel?

 

[Jeff Boyce]

Since you only used two Questions in your example, it's a little tough to
tell... but it seems like your data is organized more like a spreadsheet
(?got another question, add another column) than as you would need to in a
relational database.

Since everything starts with the data, please provide a bit more description
of how your data is structured...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

I am surprised that you cannot get the average of the scores in a query.
The average will be a negative number for the question.

If you want to know that wrong responses to question 7 represents 22% of the
total of erroneous responses, then that is a lot tougher with your
structure.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sabik]

From reading some of the other similar questions I thought that I probably
did not have the data structured properly for Access.

Here is a sample of my table TestResults:

StudentID Text
TestName Text
TestDate Date/Time
TestScore Number
Question1 Yes/No
Question2 Yes/No
.... ...
Question100 Yes/No


I have a table that stores StudentInfo, a table for TestInfo for each test,
and a table for OtherGrades.

Hope that is enough info for you to help me. Thanks

 

[Jeff Boyce]

Sabik

What you've described (Question1, Question2, ... Question100) is a
spreadsheet, not a relational database table.

With a design like this, you will be forced to modify/maintain your tables,
your forms, your queries, your reports, your macros, your code, ... if you
change the number of questions. This is neither desirable nor necessary.

With Access, think "narrow and deep", rather than "wide and shallow". For
instance, another way you could organize your data is like:

trelTestResults
TestResultsID
PersonID (a foreign key from your "person" table -- who does this
record relate to?)
QuestionID (a foreign key from the "questions" table -- which
question (i.e., 1, 2, 3, ...) does this record relate to?)
Response (if all your questions are Yes/No, this field could be a
Yes/No)
...

I'll suggest that you take a look at a sample database created by Duane
Hookom for a model of how to set up a database to handle this situation:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sabik]

Sabik

What you've described (Question1, Question2, ... Question100) is a
spreadsheet, not a relational database table.

With a design like this, you will be forced to modify/maintain your tables,
your forms, your queries, your reports, your macros, your code, ... if you
change the number of questions. This is neither desirable nor necessary.

With Access, think "narrow and deep", rather than "wide and shallow". For
instance, another way you could organize your data is like:

trelTestResults
TestResultsID
PersonID (a foreign key from your "person" table -- who does this
record relate to?)
QuestionID (a foreign key from the "questions" table -- which
question (i.e., 1, 2, 3, ...) does this record relate to?)
Response (if all your questions are Yes/No, this field could be a
Yes/No)
...

I'll suggest that you take a look at a sample database created by Duane
Hookom for a model of how to set up a database to handle this situation:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sabik]

Normalizing the data, works every time!
Thanks!

Sabik

What you've described (Question1, Question2, ... Question100) is a
spreadsheet, not a relational database table.

With a design like this, you will be forced to modify/maintain your tables,
your forms, your queries, your reports, your macros, your code, ... if you
change the number of questions. This is neither desirable nor necessary.

With Access, think "narrow and deep", rather than "wide and shallow". For
instance, another way you could organize your data is like:

trelTestResults
TestResultsID
PersonID (a foreign key from your "person" table -- who does this
record relate to?)
QuestionID (a foreign key from the "questions" table -- which
question (i.e., 1, 2, 3, ...) does this record relate to?)
Response (if all your questions are Yes/No, this field could be a
Yes/No)
...

I'll suggest that you take a look at a sample database created by Duane
Hookom for a model of how to set up a database to handle this situation:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP