records with multiple checkboxes

[Guest]

I'm trying to modify a database created by someone else. I have a table with
a TestID, StudentID and results of tests where the results are in checkboxes.
Within each test for each student there are 6 strands that I need the total
of correct responses. Each Test has a different number of questions
(checkboxes) in each strand. I can get a total number of correct responses
for each student but don't know how to get the totals for the strands.
Ex. TestID 19 has 8 checkboxes in strand 1, 5 in strand 2, 10 in strand 3 etc.
TestID 21 has 10 in strand 1, 2 in strand 5, 15 in strand 3 etc.

I have no vb background so... What would be the easiest way to find the sum
of each strand in each test?

 

[John Vinson]

I'm trying to modify a database created by someone else. I have a table with
a TestID, StudentID and results of tests where the results are in checkboxes.
Within each test for each student there are 6 strands that I need the total
of correct responses. Each Test has a different number of questions
(checkboxes) in each strand. I can get a total number of correct responses
for each student but don't know how to get the totals for the strands.
Ex. TestID 19 has 8 checkboxes in strand 1, 5 in strand 2, 10 in strand 3 etc.
TestID 21 has 10 in strand 1, 2 in strand 5, 15 in strand 3 etc.

I have no vb background so... What would be the easiest way to find the sum
of each strand in each test?

Yuck!!! Totally non-normalized data.

Any chance of extracting the data out into some normalized tables? I
could see four tables:

Students
StudentID
<student bio information>

Tests
TestID
Description

Questions
QuestionID <primary key>
TestID <what strand is this a question in>
StrandNo

Answers
StudentID
QuestionID
Answer <yes/no>

I don't understand how your current table is structured, though. How
can you tell for a given record which field is in which strand?

One hint: TRUE is stored as -1, FALSE as 0. So you can use an
expression

HowManyTrue: - ([FieldA] + [FieldB] + [FieldC] + [FieldD])

to count the YES answers.

John W. Vinson[MVP]

 

[Guest]

Thanks for the reply...
The DB has tables similar to what you refered to, with the exception of the
one tying the individual questions with a strand. I created a table with the
following:

QuestID - AutoNumber - Prim Key
TestID
QuestionNumber
StrandName

I tied this table to the others via the TestID.
I wanted to be able to query using the TestID and StrandName to get the
results for each student on each test.
I also wanted to avoid - HowManyTrue: - ([FieldA] + [FieldB] + [FieldC] +
[FieldD])
because of future modifications to the tests.

Let's say TestID 19 has 8 questions in Strand 1
When I query using TestID (19) and Strand 1, I don't get the first 8 answers
from the test; I get 8 rows of the entire test for each student.

I know this probably dosen't make any sense to you but any other thoughts or
ideas would be appreciated.

I'm trying to modify a database created by someone else. I have a table with
a TestID, StudentID and results of tests where the results are in checkboxes.
Within each test for each student there are 6 strands that I need the total
of correct responses. Each Test has a different number of questions
(checkboxes) in each strand. I can get a total number of correct responses
for each student but don't know how to get the totals for the strands.
Ex. TestID 19 has 8 checkboxes in strand 1, 5 in strand 2, 10 in strand 3 etc.
TestID 21 has 10 in strand 1, 2 in strand 5, 15 in strand 3 etc.

I have no vb background so... What would be the easiest way to find the sum
of each strand in each test?

Yuck!!! Totally non-normalized data.

Any chance of extracting the data out into some normalized tables? I
could see four tables:

Students
StudentID
<student bio information>

Tests
TestID
Description

Questions
QuestionID <primary key>
TestID <what strand is this a question in>
StrandNo

Answers
StudentID
QuestionID
Answer <yes/no>

I don't understand how your current table is structured, though. How
can you tell for a given record which field is in which strand?

One hint: TRUE is stored as -1, FALSE as 0. So you can use an
expression

HowManyTrue: - ([FieldA] + [FieldB] + [FieldC] + [FieldD])

to count the YES answers.

John W. Vinson[MVP]