Lookup Score in table on Variables

[C. Houk]

Having a problem with how to code a function I want to
have for my dbase.
Background:
Have a database for Physical Fitness scores. The scores
are calculated based on Age Group and gender. 4 Areas are
scored. Run time, Pushups, Situps, Waist size. Currently
we manually score each line item and enter it into the
database. I would like to come up with a way that the
scores are calculated automatically.
i.e. I enter under my personal record that I am Male, 30,
and have X Run time...it outputs score of 40, X number of
pushups, Gives me a score, etc.
The problem is the scores are from a set of tables that
are broken into ranges. For example. Run times of 9:37 -
9:42 = 50, 9:43-9:55 = 49.3 etc. The other exercises are
done in ranges as well. 45-50 pushups = x points etc.
How would I create a query to find this information based
on the Age and Gender Variables to provide the score.
Will I need to enter
each time individually into the lookup table?
09:37 =50
09:38 =50
09:39 =50 etc?

Thanks for your help in advance
Someone suggested using a NoN equi Query to do this, but I
am a little vague on exactly what he means.
C. Houk
charles.houk2<nospam>@charleston.af.mil

 

[Nikos Yannacopoulos]

Charles,

Assumptions for my example:

Run time scores table: tblRunScore
Run time scores fields: fldLow, fldHigh, fldScore
Actual run time for individual in main table: fldRunTime

Make a simple select query on your main table, and add a calculated field as
follows:
RunScore: DMin("[fldScore]","tblRunScore","[fldLow] <= [fldRunTime]")

HTH,
Nikos