Lookups with Composite primary keys - How To?

[mac]

Summary:
1. I want to define a column in an MS Access table to be a lookup on
another
table.
2. The table that is the data source (e.g the "parent" table) has a
composite primary key.
3. When the "child" table does the lookup, it should pass all the columns
necessary to properly restrict the data returned to the values for he
lookup.
4. How do I accomplish this in a lookup?

Please, no suggestions to avoid lookups, etc. This is for a very quick and
dirty "spreadsheet replacement" for a one time data mapping exercise I do
not have the time to write an app for this. I need to avoid code and just
use what I have defined in the table structures and foreign keys.

The following example is fabricated, but it shows the idea. I know that the
keys in the example aren't ideal, but it makes it easy to read the example.

ParentTable (Key is composite of (State, CityName):
[State], [CityName], [Population]
AZ Phoenix 1,000,000
GA Phoenix 8,000
AZ Tucson 500,000


ChildTable (Primary Key is SalesmanID, Date Assigned). State and CityName
are part of a composite
foreign key that referes to the ParentTable.
[SalesmanID], [Date Assigned] [State], [CityName]
1 01/15/2006 AZ Phoenix
1 03/01/2006 AZ Tucson
3 01/15/2006 GA Phoenix


I want ChildTable.CityName to lookup possible values from ParentTable,
but I want the values shown in the lookupto be restricted based on the State
entered
in the column ChildTable.State. For example,if you were in either of the
first two rows
of ChildTable, and hit the drop down, you should see'Phoenix' and 'Tucson'.
I
f you were in the third row, you should only see 'Phoenix', but it would
be the Phoenix with a population of just 8,000 people.

If I define the column ChildTable.CityName as a lookup, I always see the
values Phoenix, Phoenix, Tucson, regardless of which row I am on in
Childtable.

How do I define the lookup so that the value from ChildTable is part of the
WHERE clause in the query that gets datafrom ParentTable to populate the
listbox?
In other words, I want the query to say WHERE State = 'AZ' when someone
invokes the lookup from one of the first two rows in ChildTable.


Thanks,

Mac

 

[Neil Sunderland]

1. I want to define a column in an MS Access table to be a lookup on
another
table.
2. The table that is the data source (e.g the "parent" table) has a
composite primary key.
3. When the "child" table does the lookup, it should pass all the columns
necessary to properly restrict the data returned to the values for he
lookup.
4. How do I accomplish this in a lookup?

Please, no suggestions to avoid lookups, etc. This is for a very quick and
dirty "spreadsheet replacement" for a one time data mapping exercise I do
not have the time to write an app for this. I need to avoid code and just
use what I have defined in the table structures and foreign keys. [...]
In other words, I want the query to say WHERE State = 'AZ' when someone
invokes the lookup from one of the first two rows in ChildTable.

You can't do this from a lookup field on a table. You would need to
create a Form which refers to the table, with a list box whose record
source property is changed by code when you click on it.