Variable If/Then lookup

[Rob]

Background:
I am running a fitness database in which currently scores
are manually input. I want to have the database lookup
scores based on input by user.
Example:
WaistMeas field on form is filled in.
Database checks Gender from earlier Gender field on Form.
Then Checks Age from field on form.
Taking Age and Gender database looks up appropriate score
on Table and inputs score into WaistScore field on Form.
I.E. Gender = M Age = 19 so Waist Measurment of 36 =
WaistScore of X
I can build a table for all Males and break down score, or
I can build individual Tables for Gender and Age i.e. M19,
M20, F19, F20.
How would I go about writing an if/then statement with
multiple variables on the Form, or any other good way to
do this calculation would be greatly appreciated.

(e-mail address removed)

Be glad to supply structure of Database via email if it
will help.

 

[Graham Mandeno]

Hi Rob (or is it Charles?)

You need only one table, with four fields:
Gender: (M or F)
Age: (byte)
WaistMeasurement: (byte or single, depending on whether you have only whole
numbers)
WaistScore: (whatever type)

You then simply query the record with the matching Gender, Age, and
WaistMeasurement.
Select WaistScore from YourTable where Gender='M'
and Age=19 and WaistMeasurement=36

If you don't want to create records for every possible age and measurement,
you could query the first record with matching gender where the age and
measurement are equal or more than the ones provided:
Select top 1 WaistScore from YourTable where Gender='M'
and Age>=19 and WaistMeasurement>=36
order by Age, WaistMeasurement