Table structure for test question profile

[hilian]

I’m trying to create a test-taker profile. The test has seventy-five questions, and there are 130 test takers for the current administration--there will be future administrations. The profile should have three pieces of information for each test taker for each question: whether the test was answered correctly, the general content area (Topic) and the specific content (Subject).

The problem is that I’m stuck on how to normalize the tables. This has got to be a problem that’s been solved long ago, but I can’t find the solution. At present I have three tables. The first has a field for test-taker name, for an id number and seventy five fields, one for each question, each containing a Yes or a No, depending on whether the question was answered correctly. The table has 130 records, one for each test taker. The other two tables are for subject and topic. They each have seventy five fields, one for each question. Each table has one record, containing the information (topic or subject) for each question. I’ve put the three tables together in a query to create a report, but that’s awkward. It means that the query has 125 fields plus fields for test-taker name, etc. fields containing information on the questions repeats for each test-taker.

If I had one record in a main table for each test taker, would I have to have 75 related records in a questions table for each test taker, making 9750 records for the 130 test-takers? That seems like a lot of records for one test administration. Would the questions table be linked to a question information table with 75 records, one for each question?

Many thanks,

Henry

 

[MarkK]

Every discrete thing in a system should have it's own row in a table, so each test, each test taker, each question, each answer, each discrete thing should be in it's own row. And you may need additional tables to mediate many-to-many relationships between some discrete things, like if a test-taker might take the same test on different dates, then you have an abstract object which is the dated event of the test-taker taking the test. This is a discrete thing that is neither the test, nor the taker, but still a discrete entity that requires its own row.
Hope this helps,

 

[The_Doc_Man]

Search this forum for the topic "Questionnaire" - which has been discussed many times by various members over the years.

 

[hilian]

Mark,

That does help, but it's hard to translate the general principle to the specific problem. I did find a model for a test db on line, but it wasn't close enough to what I need to adapt. I'm going to follow The_Doc_Man's suggestion and look up "Questionnaire" to see if it gives me the guidance I need.

If it doesn't I'll try to to work out the principle, "each discrete thing should be in it's own row," and ask a more specific question if I get stuck.

Thanks,

Henry

 

[Uncle Gizmo]

You may find this blog (both text and video) useful...

https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

This blog shows how to create a checklist. I posted a video somewhere showing how to change the checkbox into a text box so that you can record text instead of boolean.

I think there is a way to have a combo box as well. You could then have multiple choice answers for each question. However it's not straightforward, I haven't sussed out the details yet.

https://sites.google.com/site/msacc...-your-db/videos---add-a-check-list-to-your-db

Sent from my SM-G925F using Tapatalk

 

[hilian]

Hi Uncle Gizmo,

I looked over the blog, and the example he gives is right on target. Of course, I would have a table with questions rather than subject, but it’s the same idea.

I would have four tables: 1) tblTest_Takers, with a name field, an ID field and a class ID field; 2) tblQuestions, with a question ID, a test-taker ID, and a field (possibly Boolean) indicating, for each test taker, whether the question was answered correctly or not; 3) tblQuestion_Information, would have question ID, and the subject matter covered; and 4) tblTopics, would have the general topic area for each question. My hesitation in constructing this is that there would need to be 9,750 records for the 130 test takers for 75 questions. I know Access can easily handle this many records, but over many administrations, the number would become unwieldy. I would also need to restructure the data for every test administration--I saw the normalization tool, but I haven't looked into it.

One possibility would be to wipe out tblTest_Takers after the report was run. There won’t be any need for the data in this format after the report.

Is there a way to structure the db so there isn’t a need for this many records? I would also like to avoid the need to restructure the spreadsheet data each time to populate second table.

Thanks,

Henry

 

[Minty]

That number of rows really isn't an issue. We store ongoing status updates for items in our database and they are approaching 1,000,000 for approximately 40,000 related "master" records. It's perfectly usable.

You would probably want to add a top level identifier to the test takers row indicating which year or period they were for, so that you can easily filter out past history.

 

[hilian]

Minty,

Part of my reluctance to create a large number of records was that I would have to convert the data from a spreadsheet format, with 75 test questions in the columns and test-taker answers in the rows, to one in which there was one field for all answers for all questions for all test-takers, the 9750 records.

I’m aware that I could do it with code, but I’m more comfortable with Excel formulas, so I decided to do it that way, and I was able to move forward. In a workbook, I created four worksheets: one has the test data. The others are: Test_Takers, Answers, and Question information. The Answers table contains the correct/incorrect answers for each question for each test-taker (9750 records). I was able to use Excel formulas to reference the data in the test data worksheet put it into the Answers worksheet in the vertical format I needed. There are three fields, Test-Taker ID, Question ID and Correct/Incorrect. The other worksheets contain test-taker information and question information.

Instead of importing the spreadsheets into Access as tables, I linked them. That way, I can replace the data for every test administration, and I don’t have to worry about myriads of records I’ll never use. I simply have to update the links. In Access, I related the three tables in a query and got information I need for the report in the format I need.

Many thanks for your clarification.

Henry