Conparative Setups

G

groupware

Hi,

I have been asked to look at a database that essentially holds the
scores(eg1-5) for a series of questions.

The problem that has been posed is:

"If we were to change/add/remove/join questions how do we maintain
comparability for reporting purposes?"

I would appreciate peoples thoughts or altenrative approaches to
achieveing this.

To date I have thought some form of mapping functionality would work,
but this needs to be future proof as I am pretty sure ther will be
future changes.

Thanks in Advance

Jason.
 
M

[MVP] S.Clark

You would never alter an existing question. Instead, you would make it
inactive. You would create a new question, and then be able to compare,
relatively, the old and the new.
 
G

Graham Mandeno

Hi Jason

Do you mean that question 5 in this week's survey/test might be Q6 in next
week's because a new Q4 has been added and Q2 has been replaced?

If so, what I would do is associate each question with a unique ID (use an
AutoNumber). The actual questions in each test can be recorded in another
table, with relationships to both the Tests table and the Questions table.
For example:

TestID (foreign key in relationship with Tests)
QuestionID (FK in relationship with Questions)
QuestionNumber (defines the order that the Qs appear in this test)

Make TestID and QuestionID a two-field composite primary key so that you
can't have the same Q twice in the same test.

You could also make TestID and QuestionNumber a unique secondary key so that
you can't have two Qs with the same number in a test.

Your results can be recorded in a table with:

CandidateID
TestID
QuestionID
Score

It is then easy to compare scores for the same Q in different tests, even
though they might have had different question numbers.

Adding to what Steve said, if you want to change a question you should add a
new record to the Questions table (with a new QuestionID) and include that
new question in the next test, rather than changing the text of an existing
question.
 
G

groupware

Thanks for both your replies.

That approach should work in the majority of cases.

However one of the problems is if I currently have two questions:

An illustratvice example:
QId1 Q#1. How many cats do you own Answer: 3
QId2 Q#2. How many dogs do you own Answer: 2

and say I now want this Q to be (with a new answer)

Qid? Q#1. How many cats and dogs do you own. Answer 8

How do I keep the comparability between ongoing results assuming we
complete the same questionairre reguralary and need to report like for
like results ? So that now if I run a report I can see 5 and 8 as the
historic responses to the quesion(s) ?

So this is where I thought some mapping table might come in, but I see
it getting very messy over time.

Any further thoughts ?

Thanks

Jason.
 
M

[MVP] S.Clark

Let's suppose you drove from NY to CA, marking the odometer at the start and
finish. How would you if any miles were travelled in MO? How could you
know how many miles you traveled in MO? The obvious answer is, "Not by
looking at the odometer".

Convince the silly question writer that you can't combine two questions,
then expect to know the individual parts of the composition, unless, of
course, you ask that in the next question(s).
 
G

Graham Mandeno

Hi Jason

If this happened infrequently, you could simply create response records for
the new question (call it Qid3) from an append query adding the responses
for Qid1 and Qid2 linked by CandidateID and TestID.

If it happened frequently, then you would have to question the sanity of the
survey designer! However, you could design a system of "virtual questions"
which are based on the some expression involving the responses to "real"
questions.

For example, the number of cats and dogs would be the sum of Qid1, Qid2 and
Qid3 (assuming Qid3 never occurred in the same test as 1 and 2!!)

Average monthly income could be Qid27 / 12, where Qid27 asks for annual
income.

Of course, as Steve says, you can't go backwards. How many of those 8
cats'n'dogs were feline?
 
G

groupware

Steve,

firstly if you don't have anything contructive to say then I think you
would be better off keeping your comments to yourself.

I don't want to be able to know the component parts of a a question.
i.e using my example how many cats and how may dogs. But I do want to
know that if the answer to that question is now 8 to be able to compare
it to the previous answers which were (3+2)= 5.

That's all. Messy and not of my making. Just looking to see if anyone
had any createive ways around this.

Jason.
 
G

groupware

Graham,

Thanks for reply.

I will give a few things a try. (I have questioned the sanity of the
person who has built this ! and cursed a bit too)

We will be marking the previous (old) questions as incative so they
won't be used in the same questionairre as the new combined ones. So
that shouldn't be a problem.

Thanks

Jason.
 
Top