How do I create a relationship to similar match in Access?

[Joana Villas-Boas]

I have a table with minimum and maximum values to find out the ideal weight
for each heigth of the Client's Table.

I wan't to put the client hight and need access to Lookup on the other table
wich is the ideal weight for that person.

I am an expert in all sort of relationships, but don't know how to do this
one once it is not a direct relationship, it works more like the Lookup
function in excel.

Is this possible in Access?

 

[Jeff Boyce]

It sounds like a query would work. Since you didn't provide a specific
example of the values, it's a bit hard to offer a specific suggestion.

If your query looked for values in table2 where the value from table1 was >=
MinimumValue and <=MaximumValue, would that work?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joana Villas-Boas]

Thank you Jeff,

I already tried the query and it works fine, now I need to find out how to
make it work on the client's form.

When I have a new client and I input his weight and heith, the ideal weight
is supposed to appear on another field. I can't have that query on the source
of my form. I must figure out another way..

I will sleep on that next issue, maybe you know some way around that too?

Thank you again,

best reagrds,

Joana

 

[Jeff Boyce]

Joana

So, it sounds like you're saying that after you update the controls with
height and weight, you want the calculated (looked-up) value to show in a
(hopefully-unbound) textbox on the form?

You could add a procedure to the form that first checks to see that both
values are entered, uses either a query or DLookup() function to get the
looked-up value, and fills the textbox with the value. You could then call
that function in the AfterUpdate event of each control, plus add another
call when the form gets an existing record (OnCurrent).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joana Villas-Boas]

That's exactly what I want to do and I thought about the Dlookup function,
but the Lookup Table it's a bit complicated once it has a max and min Heith
and Max and Min Age for each Ideal Weight. I dont' know how to do the Dlookp
with so many fields to lookup into!

I did what you suggested with the Query and it works fine, the problem is
the form is not updatable because there are no relationships between the
client and the ideal wheight table!

Thank you anyway, you helped a lot.

Joana

 

[Jeff Boyce]

Please note that it is rarely necessary to store a looked-up value if you
can re-calculate it.

Regards

Jeff Boyce
Microsoft Office/Access MVP