Design question - tables with scores

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

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!
 
J

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
 
S

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top