Correct table set up for later calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stilla - 14 Jun 2007 06:35 GMT

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

Reply to this Message

Wayne-I-M - 14 Jun 2007 07:55 GMT

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.

Signature

Wayne
Manchester, England.

> 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
[quoted text clipped - 15 lines]
> Product 2 1 2 3
> Product 3 2 3 3

Reply to this Message

Stilla - 14 Jun 2007 13:37 GMT

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

> Hi
>
[quoted text clipped - 43 lines]
> > Product 2 1 2 3
> > Product 3 2 3 3

Reply to this Message

John W. Vinson - 14 Jun 2007 20:57 GMT

>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
[quoted text clipped - 9 lines]
>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]

Reply to this Message