Help Scoring Tables

[robdunfey]

I have a 2 tables.

Table One:
1st Column is a list of persons and is called PersonID, entries might be:

Joe
Rob
Mary

Each person was asked 10 questions, the answers to each question are stored in a column. So we have 11 columns in total, 10 (one for each question) and 1 for person. Question 1 might have been "What is your fav colour?". They had a choice of three, so values in column 2 are "blue", "red" or "yellow". Q2 Do you like eating? Possible answers "Yes" or "No".

PersonID Q1 Q2 etc
Joe blue no
Rob blue yes
Mary red yes

Table 2:

This has three columns. 1st has the question number, 2nd possible answers, 3 a score.

EG:

Q A S
1 blue 50
1 red 70
1 yellow 65
2 yes 20
2 no 0

How do I go about getting a table of scores EG:

PersonID Q1 Q2
Joe 50 0
Rob 50 20
Mary 70 20

I keep thinking of different ways but Im really getting confused and lost. There must be a really simple way? But I cant see it! I am new to this programing so if you know of a way other than VBA enlightenment would be much appreciated. I know this is a long winded explanation. Thanks for your time.

Rob

 

[jvonbokel]

Just off the top of my head, I'd imagine an easier method would be to create a separate table for each Question. For example:

Table Q1:
A S
blue 50
red 70
yellow 65

Table Q2:
A S
yes 20
no 0

John