If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
records with multiple checkboxes
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? |
#2
|
|||
|
|||
records with multiple checkboxes
On Thu, 2 Mar 2006 12:00:28 -0800, "ocsshrw"
wrote: 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] |
#3
|
|||
|
|||
records with multiple checkboxes
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. "John Vinson" wrote: On Thu, 2 Mar 2006 12:00:28 -0800, "ocsshrw" wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Matching records within one table on multiple fields | Rose | Running & Setting Up Queries | 3 | February 23rd, 2006 01:59 PM |
Need to select a certain X records after a query in access | ab | Running & Setting Up Queries | 17 | October 11th, 2005 03:05 PM |
Linking multiple records | David Price | General Discussion | 1 | April 15th, 2005 11:28 PM |
Number of multiple records | Brenda Hutton | Worksheet Functions | 2 | December 15th, 2003 01:08 PM |