inter-related lookup columns?

[Kim M.]

Am creating a database for a real estate company. Among other things, there
is a Clients table that includes a ClientName field and a Properties table
that includes an Address field.

In Clients table, there is a mulitple-value-permitted "Target Properties"
lookup field using the Address field in the Properties table as possible
values. And in Properties table, there is a multiple-values-permitted
"Target Clients" field using the ClientName field in the Clients table as
possible values.

Question is: How can I link these two lookup fields, so that if I check an
additional "Target Property" for a given client in the Clients table, it
automatically updates the "Target Clients" list for that property in the
Properties table?

TIA,
Kim M.

 

[John W. Vinson]

Am creating a database for a real estate company. Among other things, there
is a Clients table that includes a ClientName field and a Properties table
that includes an Address field.

In Clients table, there is a mulitple-value-permitted "Target Properties"
lookup field using the Address field in the Properties table as possible
values. And in Properties table, there is a multiple-values-permitted
"Target Clients" field using the ClientName field in the Clients table as
possible values.

Question is: How can I link these two lookup fields, so that if I check an
additional "Target Property" for a given client in the Clients table, it
automatically updates the "Target Clients" list for that property in the
Properties table?

TIA,
Kim M.

Lookup fields are very limited.
Multivalue lookup fields are VERY LIMITED.

See http://www.mvps.org/access/lookupfields.htm for a critique of what many of
us consider a misfeature that never should have been put in to Access.

Short answer? You can't link the lookup fields. Microsoft hid the junction
table that is actually there, so you can't get at it to link it.

The better solution is to create your OWN junction table. Don't use any
lookups, and don't use any multivalue fields, period. Instead create a new
table, Ownership, with fields for the PropertyID and the ClientID (DON'T use
ClientName as a key field in the Clients table, you very well might have two
different people who happen to have the same name). You can then add as many
records as you want to Ownership, so if one owner owns several properties, or
one property is owned jointly by several owners, you can record each such
relationship as a record in the table.

 

[Kim M.]

Thanks John. I'll try it that way.

Kim M.

Lookup fields are very limited.
Multivalue lookup fields are VERY LIMITED.

See http://www.mvps.org/access/lookupfields.htm for a critique of what many of
us consider a misfeature that never should have been put in to Access.

Short answer? You can't link the lookup fields. Microsoft hid the junction
table that is actually there, so you can't get at it to link it.

The better solution is to create your OWN junction table. Don't use any
lookups, and don't use any multivalue fields, period. Instead create a new
table, Ownership, with fields for the PropertyID and the ClientID (DON'T use
ClientName as a key field in the Clients table, you very well might have two
different people who happen to have the same name). You can then add as many
records as you want to Ownership, so if one owner owns several properties, or
one property is owned jointly by several owners, you can record each such
relationship as a record in the table.