Calculation Lookup

[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 lookup to a table to find this
information 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

C. Houk
charles.houk2<nospam>@charleston.af.mil

 

[Kevin Sprinkel]

No; I think you can enter only the lower threshold, and
then use the Max function to select the largest value in
the "lookup" table which the given value exceeds.

HTH
Kevin Sprinkel

 

[John Vinson]

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 lookup to a table to find this
information to provide the score.

Well... FORGET about "lookup fields". They are one of the worst
misfeatures Microsoft ever half-implemented!

Instead you should use a Query to look up the data. You cannot do this
in a Lookup Field anyway (even if Lookup Fields *were* of any use) but
you can create what's called a "Non Equi Join" query. You'll need to
go to the SQL window to create this query, but it's not too hard;
perhaps you could post the relevant table and field names, and I could
give you an example to get you started.

 

[C. Houk]

I mispoke, I didn't mean the lookup function, I meant a
table that I would query and lookup the data needed. In
any case.

Form named: Fitness Test
Data Stored in Table: FitTest
Fields: <Runtime> <Runscore> <Pushup> <PushScore> <Situp>