Design question - tables with scores

  • Thread starter Thread starter MB
  • Start date Start date
M

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
Click to expand...
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
Click to expand...

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!
 
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
 
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.
 
Back
Top