How to set up and compare pre/post test answer data?

You can also join two separate instances of a table in a single query, giving each an alias to differentiate them.  You'd return the same column from each instance of the table as two separate columns and compare the two values.  Each instance of the table would be restricted in the query's WHERE clause to pre- or post-program responses.

I'd probably do it this way, but if you are not comfortable with writing queries which go beyond what can easily be done in query design view, you might find Scott's suggestion of creating separate queries and joining them simpler.

Hey Tom, 

Thanks so much for your reply! I'm sorry if I was unclear. Let me be more specific,

Yes the pre-test and post-test are exactly the same. We administer the pre-test before the program with questions like:

How much do you agree with the following statements?

1. I like math.

Strongly Agree, Agree, Neutral, Disagree, Strongly Disagree

2. I like to work with my hands.

Strongly Agree, Agree, Neutral, Disagree, Strongly Disagree

and so forth for the next 18 questions.

Then after the students complete the program, we administer the survey (the post test). And what I'm looking to do is compare the answers to the two tests for each question and see if there's an increased interest in a particular question. We do this for each student in the program.

What would be a good way to organize the data. I'm not familiar with Access that much, but I can kind of follow what you have been saying.

Thank you so much again!

I mostly agree with Ken's structure but would take a slightly different approach,. 

Tests

....TestID (PK)

....TestName

....etc

Students

....StudentID (PK)

....FirstName

....LastName

....etc

Questions

....QuestionID (PK)

....Question

....TestID (FK)

StudentResponses

....StudentReponseID (PK)

....StudentID (FK)

.....PostorPre

....ResponseDate

Answers

....AnswerID (PK)

....StudentResponseID (FK)

....QuestionID (FK)

....Answer

The StudentResponses table would record when a test was given and to who. The Answers table would include a FK to link to that table to identify what the answers are for.

In database terms a student's response to a test is a many-to-many relationship type between students, questions and answers.  So you'd need tables along these lines;

Tests

....TestID (PK)

....TestName

....etc

Students

....StudentID (PK)

....FirstName

....LastName

....etc

Questions

....QuestionID (PK)

....Question

....TestID (FK)

Answers

....AnswerID (PK)

....Answer

The relationship type between questions and answers would be modelled by a table:

QuestionAnswers

....QuestionID (FK)

....AnswerID (FK)

The primary key of this table is a composite one of both columns.

The relationship type between students, questions and answers is modelled by a table:

StudentResponses

....StudentID (FK)

....QuestionID (FK)

....AnswerID (FK)

....AnsweredWhen

The AsweredWhen column would have possible values Pre-program and Post-program.  This table is again all-key, i.e its primary key is a composite one of all four columns.

You'll appreciate that these are only preliminary thoughts and the model should be tested thoroughly and fine-tuned if necessary.  On the whole I think the structure is about right, though.

I'm not sure I have enough information to be precise, but for starters I would save all answers in one table.

Assuming the pre-test questions and the post-test questions are the same, we need to support the notion that the same test is given to the same student multiple times (typically: twice). I'm thinking of a table TestInstances which gets a record each time a test (= a set of questions) is given:

TestInstanceID autonumber PK

TestDate datetime required

TestID long integer, FK, required

IsPreTest yesno, required, default=Yes, Comment=If False, it's a PostTest.

Each TestInstance record has a set of answers in the Answers table:

AnswerID autonumber PK

QuestionID long int, required, FK

TestInstanceID long int, required, FK

StudentID long int, required, FK

Answer text, required  (could also be a number 1-5)

Does this get you started?