Test Sheet Form

[lraffel]

Hello,

I am trying to design a form that functions as a test.

Section 1: Student Name, Date, Test they are taking

Section 2 (this I need help with): Currently I have a subform that allows me to select the question I want and enter the score. However with 30 questions per student per test this is getting out of hand - each of these is 1 record.

I would like a form/subform that gives me a test template that I can customize. So in section 1 I select Test A and it pulls up the template for Test A. For some questions, some students will have to answer a question more than once. So for Question 5, I want the option to answer the template answer, then to create a record for the second answer for Question 5.

Any ideas? To create the test template? Auto-pull the right template based on an entry in the main form? And/or to add records in the middle of the template?

Thanks!

[Rod]

I do not fully understand your post. You start by talking about questions and then switch to talking about answers. The part about a student answering a question more than once I find particularly confusing. Never mind I think I understand enough to make a start.

Let's start with some relational data design. I identified three entities from your account:

Student
Test
Question

There are probably many more such as Class, Moderator, etc. but we'll start with the three you mentioned.

I think the relationships between the three entities are many-to-many (m:m). That is a student may take zero to many tests and a test may be taken by zero to many students. Likewise there is a m:m relationship between tests and questions if questions are reuseable across tests. (I leave the other relationship out of it for the moment; it may or may not be needed.)

Many to many relationships are quite OK for relational data analysis but not good for relational database design. We therefore invent a table that cross references each entity in such a relationship. Hence the three entities are implemented as (at least) five tables in the database design; the many to many relationships are resolved into one to many relationships. However I suspect you are interested in the questions/answers of a particular student for a particular test. For this we need to create an additional table with all three entities as foreign keys.

The relationship diagram is attached.

I hope you can see that the tblTestQuestionXref is the 'template' you are talking about while tblTestResult far from being another cross reference becomes the repository for all test results at a question level.

With this design you can design a master form bound to a particular test and then have a list of all students taking that test appear on the form for you to select a particular individual. With a little adroit programming you can then show in a subform all the questions for the selected student for the particular test. The subform is scrollable and updateable.

Is this the kind of thing you had in mind?

[lraffel]

Thanks for responding to my post. It was up for so long I wasn't sure anybody would. Attached is my database design, and it looks a lot like yours short 1 table. I think what you start to talk about in the last paragraph in your post is what I am interested in doing, just don't know how.

"With this design you can design a master form bound to a particular test and then have a list of all students taking that test appear on the form for you to select a particular individual. With a little adroit programming you can then show in a subform all the questions for the selected student for the particular test. The subform is scrollable and updateable." - Exactly! But how to do the subform? I have a start on the master form.

[Rod]

I'll get back to you later today. I'm GMT +8 hours.

[boblarson]

Attached is my database design,

Your structure looks good for the most part - just that you have QuestionID in the table you have named tblTestResult (which is actually better named tblTestHeader as the results are really in tbl_TestResultDetails) so it should be removed as QuestionID already exists in tbl_TestResultDetails.

I would say that Rod's design is not quite there (and you can usually tell that something isn't quite right if the relationship lines form essentially a circle, which his does).

See the pic for my suggested structure.


Also, for populating the subform you can use an append query which appends the questions for the selected test/student. We can put up an example of that later.

[Rod]

Bob: Give me a chance. That was a first cut and I wanted to confirm with Iraffel that we were going in the right direction before refining the database design.

Iraffel: Circular relationships occur in real life many times over. They are OK at the data analysis stage but can be cumbersome to handle in a physical database design, not to mention the processing overhead. I have always been in the habit of defining all relationships at the entity relationship diagramming (ERD) stage. Then refining that diagram to produce a physical database design makes me aware of what sacrifices and compromises I am making. The discipline of asking, “What are the implications of doing this?” as I make each refinement help me explain the features and limitations of my design. For example in both Bob’s and your designs there is no convenient way of specifying and retaining the test template – i.e. the questions related to this test. You will have to use some trick using a ‘dummy’ student to define the test template. This may be of no consequence for you but it should be pointed out right now at the beginning of the project. Again in your design there is no convenient way of keeping a schedule (future and past) of students vs tests. Is this important?

Yes I had in mind something akin to an append query for populating the test result table (hence subform) when I mentioned ‘adroit programming.’ But in both Bob’s and your designs, what is the source for this append query?

The more important question is not how but when to populate in the life cycle. The earlier you do it the more chance there is of having to update the populated data if any change is made to the source. The best time is immediately before you enter the results for a particular student. This will make the subform display a little sluggish – probably unnoticeable on the likely volumes – but avoids having to keep the data up-to-date in the interim.

To avoid confusion I won’t post another design

[boblarson]

But in both Bob’s and your designs, what is the source for this append query?

The questions table. But I see what you're saying about the template as it isn't just one test that we'd be dealing with. We would need to have a Test/Question cross ref table to assign the questions to the particular test.
And sorry about the way I had worded my reply. While there may be times that the relationships may display a circular path, I have found that it is a good rule of thumb to re-examine them carefully if you find that you have such because the majority of the time, the design is incorrect instead of really needing the circular route. So, the circle should be counted as sort of a red flag which needs to have the design more carefully scrutinized and potential design changes can be made which would not present such a situation.

[Rod]

I agree with all that. It would be enjoyable and fascinating to spend a couple of days discussing and debating data analysis and database design but our immediate task is to get Iraffel the best db design. I hope we haven't frightened him/her away. Are you there, Iraffel? Post again and either Bob or I will guide you through.