Looking information in rows and columns

[mikejerez]

Hi folks, I have a new problem I have been working on for a while. I have a table which contains the weight tolerances for 4 different age ranges based on their height. So, the first field (column if viewed in Datasheet view) has the height and the subsequent fields (Columns) have the maximum weight for their respective age range (18-21, 22-26, 27-30).

I want to create a form where I input a person's age, height and weight and it goes to this table to compare the data and tell me whether the person is within tolerance. So it'll go to the table and go to the field where his age and his height intersect and compare the weight with the weight value at that intersection.

I hope this does not sound confusing.

Thanks,

 

[MarkK]

You haven't asked a question, and the problem you describe has many facets. To better elicit useful information consider asking a question or presenting a more specific problem.
Cheers,

 

[stopher]

Hi folks, I have a new problem I have been working on for a while. I have a table which contains the weight tolerances for 4 different age ranges based on their height. So, the first field (column if viewed in Datasheet view) has the height and the subsequent fields (Columns) have the maximum weight for their respective age range (18-21, 22-26, 27-30).

I want to create a form where I input a person's age, height and weight and it goes to this table to compare the data and tell me whether the person is within tolerance. So it'll go to the table and go to the field where his age and his height intersect and compare the weight with the weight value at that intersection.

I hope this does not sound confusing.

Thanks,

To start with you should not design your lookup table like you would in a spreadsheet. Instead, each combination of Height/AgeRange needs to be a separate record in a table. So if you have 10 heights and 3 age ranges then you will have 30 records. By doing this, the lookup becomes trivial.

Once you have structured your lookup table as above, there are several ways to approach this. Some I can think of:

• Use a query that uses the forms inputs as a criteria to search the lookup table
• Use the DLOOKUP function to lookup values from your lookup table based on inputs from the form
• Do the lookup in VBA (code) using recordsets.

I attach an example using the first method. Take a look at the form. All the button does is forces the underlying query to refresh its data based on the current form entries.

hth
Chris