Correct table set up for later calculations

[Stilla]

Thanks in Advance! I am planning a simple entry form and database that will
store ratings of the same product by multiple raters. I will later want to
calculate simple percentages or average ratings for the products, and build a
simple report that shows these results (I have questions about this too, but
I'll save them till after this one is answered)

I'm a bit muddled as to whether a "record" should represent a rater or
whether a record should represent a product. In other words, should the
table look like the first example or the second below?

Product 1 Product2 Product3
Rater1 2 3 1
Rater2 1 2 3
Rater3 2 3 3

rater 1 Rater 2 Rater3
Product 1 2 3 1
Product 2 1 2 3
Product 3 2 3 3

 

[Wayne-I-M]

Hi

It depends on what rating actually is. You will need a table containing
data on the products thats for sure. You will not be able to "rate" anything
if you don't.

If the rating an average score awarded to a product you will need a table
also to hold these scores.

EG.
tblProducts
ProductID = Autonumber
Product = Text
etc
etc

tblScores
ScoreID = Autonumber
ProductID - Number (linking field for the relationship)
Score = Number
ScoreAwardedBy = Text
etc
etc

Use the relationship window to form the relationship.

Try out different designs and see what works for your needs first.

 

[Stilla]

Thanks for your response Wayne. Maybe I wasn't all that clear...
yes, of course I understand I need a table, that's why I wanted to know how
to set it up correctly.

Let's say I will be interviewing 10 people about product A, 10 other people
for product B and 10 other different people about product C. Let's say I'll
be asking about taste and appeal, each on a 3 point scale. I will need to
calculate either % or average across those raters who rated each product.

What is confusing me is that not all 30 people are rating the same product

Would I set up the table like this so I can average down the column? (the
dashes mean no data - these raters did not rate this product)

Prdct A taste Prdct A appeal Prdct B taste
Prdct B appeal
rater 1 2 1 -
-
rater 2 3 3 -
-
rater 3 1 2 -
-
etc...

rater 12 - - 2
3
rater 13 - - 1
2
rater 14 - - 3
1
rater 15 - - 3
1
etc.

THANKS AGAIN

 

[John W. Vinson]

I'm a bit muddled as to whether a "record" should represent a rater or
whether a record should represent a product. In other words, should the
table look like the first example or the second below?

Product 1 Product2 Product3
Rater1 2 3 1
Rater2 1 2 3
Rater3 2 3 3

rater 1 Rater 2 Rater3
Product 1 2 3 1
Product 2 1 2 3
Product 3 2 3 3

Neither. A record should represent A RATING: each rater may rate multiple
products, and each product may be rated many times, so you need *three*
tables:

Raters
RaterID
LastName
FirstName
<other biographical data as appropriate>

Products
ProductID
ProductName
<other info about the product>

Ratings
RaterID <link to Raters, who did the rating>
ProductID <link to Products, what were they rating>
Rating <number, how they rated it>

John W. Vinson [MVP]