Lookup filtered by another field in the same record

[s300]

Dear All,

I have 3 tables as described below:
<Countries>
Country_ID (key)
Country_Name
<Cities>
City_ID (key)
City_Name
Country (looked up from the <Countries> table)
<Customers>
Customer_ID (key)
Customer_Country (looked up from the <Countries> table)
Customer_City (looked up from the <Cities> table)***

***Objective: in the table, i want to have only those cities displayed in
the combobox which correspond to the Country selected for the particular
Customer.
To do this, I designed a lookup field using the query builder from the table
desgner.
The query has all three tables in it, with one output field being [Cities]
from the <Cities> table, and the other one [Country] from the same <Cities>
table. The latter is filtered by [Customers]![Customer_Country], but this
doesn't work correctly. Now if I specify a numeric value there, it does
select cities for the specified Country_Id. But it doesn't do it in the table
if the above expression is used.
(a) is it at all possible (it should be, I presume)
(b) how to achieve it?

Thank you in advance,
s300

 

[Jeff Boyce]

Using the lookup datatype in a field definition in a table may cause you
headaches down the way, and will certainly confused anyone else trying to
understand your table structure. This is because doing this stores one
value (the ID), but displays a different value (the "looked up" value).

Besides, working directly in the tables is very limiting. While this might
be the only way to do it if you were using a spreadsheet, Access offers
"forms" as the preferred method for entering/editing data. In Access,
tables store data, forms display it.

Use a form instead, after first changing those fields back to numeric (or
whatever the underlying ID types are). Use combo boxes in your form.
Search for "cascading combo boxes" to accomplish what you've described.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Dear All,

I have 3 tables as described below:
<Countries>
Country_ID (key)
Country_Name
<Cities>
City_ID (key)
City_Name
Country (looked up from the <Countries> table)
<Customers>
Customer_ID (key)
Customer_Country (looked up from the <Countries> table)
Customer_City (looked up from the <Cities> table)***

***Objective: in the table, i want to have only those cities displayed in
the combobox which correspond to the Country selected for the particular
Customer.
To do this, I designed a lookup field using the query builder from the table
desgner.
The query has all three tables in it, with one output field being [Cities]
from the <Cities> table, and the other one [Country] from the same

table. The latter is filtered by [Customers]![Customer_Country], but this
doesn't work correctly. Now if I specify a numeric value there, it does
select cities for the specified Country_Id. But it doesn't do it in the table
if the above expression is used.
(a) is it at all possible (it should be, I presume)
(b) how to achieve it?

Thank you in advance,
s300

 

[s300]

Thank you,

So I figured, though this contradicts my perhaps erroneous belief in that
the database should be initially designed at the table-level with all the
means to avoid incorrect data entries. Using the form occured to me as well,
but this could lead to incorrect data entry if the table is used instead.

Still, I guess you are right and the form approach is somewhat easier. Thank
you!

Using the lookup datatype in a field definition in a table may cause you
headaches down the way, and will certainly confused anyone else trying to
understand your table structure. This is because doing this stores one
value (the ID), but displays a different value (the "looked up" value).

Besides, working directly in the tables is very limiting. While this might
be the only way to do it if you were using a spreadsheet, Access offers
"forms" as the preferred method for entering/editing data. In Access,
tables store data, forms display it.

Use a form instead, after first changing those fields back to numeric (or
whatever the underlying ID types are). Use combo boxes in your form.
Search for "cascading combo boxes" to accomplish what you've described.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Dear All,

I have 3 tables as described below:
<Countries>
Country_ID (key)
Country_Name
<Cities>
City_ID (key)
City_Name
Country (looked up from the <Countries> table)
<Customers>
Customer_ID (key)
Customer_Country (looked up from the <Countries> table)
Customer_City (looked up from the <Cities> table)***

***Objective: in the table, i want to have only those cities displayed in
the combobox which correspond to the Country selected for the particular
Customer.
To do this, I designed a lookup field using the query builder from the table
desgner.
The query has all three tables in it, with one output field being [Cities]
from the <Cities> table, and the other one [Country] from the same

table. The latter is filtered by [Customers]![Customer_Country], but this
doesn't work correctly. Now if I specify a numeric value there, it does
select cities for the specified Country_Id. But it doesn't do it in the table
if the above expression is used.
(a) is it at all possible (it should be, I presume)
(b) how to achieve it?

Thank you in advance,
s300