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

 

[Steve]

TblPipeSystem
PipeSystemID
<Pipe system description fields>

TblPipeMaterial
PipeMaterialID
PipeMaterial
PipeMaterialPoints

TblPipeAge
PipeAgeID
PipeAgeUpperLimit
PipeAgeLowerLimit
PipeAgePoints

TblPipePressure
PipePressureID
PipePressureUpperLimit
PipePressureLowerLimit
PipePressurePoints

TblPipeInspection
PipeInspectionID
PipeSystemID
PipeInspectionDate
PipeMaterialID
PipeAgeID
PipePressureID

If you need help setting up the database, I can set it up for you for a very
reasonable fee. Contact me at (e-mail address removed).

Steve

 

[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.