Design question - tables with scores

[MB]

How do I set up a database where the items in each table has a value? For
example:

tblPipeMaterial would include:
iron=6
aluminum=8
PVC=2

tblAge would include

50 yrs = 5

40-50 = 4
30-39 = 3
20-29 = 2
10-20 = 1
< 10 = 0

tblPressure would include
<80 = 0
81-100 = 2
101-120 = 3
121-140 = 6

141 = 8

I would make each table a look-up from a list, but how do I set it up so
that when I run a report it will show the total score for each? (For
example, a PVC pipe that’s 25 years old with a water pressure of 82 would
have a total score of 6.)

Your expertise is greatly appreciated!

 

[Jeff Boyce]

A small suggestion on the side...

Don't use "Age". A pipe that is in your category 3 THIS YEAR may be in your
category 4 NEXT YEAR. Instead, use a field to hold something like
[DatePlacedInService], then use a query to determine the 'age'. This
approach has the advantage of always comparing to today's date, completely
bypassing the need to go through the database every day and update the
"AGE"!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Don't let the money-grubbing Steve scare you off, MB

Everyone other than him adheres to the newsgroup's intended purpose of free
assistance.

Post back if you have any further questions.