Lookups with Composite primary keys - How To?

[mac]

Summary:
1. I want to define a column in anMS Access table to be lookups on other
tables.
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 litbso 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 (Key is SalesmanID. State and CityName are part of a composite
foreign key that referes to the ParentTable.
[SalesmanID], [State], [CityName], [DateAssigned]
1 AZ Phoenix 1/15/2006
1 AZ Tucson 3/01/2006
3 GA Phoenix 1/15/2006


I want ChildTable.CityName to be a lookup possible values from ParentTable,
but I want the values shown in the lookup
to 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'. If 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 data
from 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

 

[pietlinden]

Access != Excel.

use joins in a query.

 

[Mac]

I should not have even mentioned the "spreadsheet replacement". I am
well aware of the differences between databases and spreadsheets, and
I'm very comfortable with the concept of relational joins. I was just
trying to say that I want to define the lookup in the Access table
definition based on a composite key. Is this possible?

Thanks,

Mac

 

[Douglas J. Steele]

Regardless of how "rushed" this may be, you should never be working directly
with tables. Creating a simple form, and then have a proper combobox do the
lookup for you doesn't take that much time.

 

[Mac]

Hi Doug,

Thanks for the info. However, my question is still unanswered. Can I
define a lookup in a table to work on a composite key?

Thanks,

Mac

 

[RoyVidar]

Hi Doug,

Thanks for the info. However, my question is still unanswered. Can I
define a lookup in a table to work on a composite key?

Thanks,

Mac

I think you can assume that the answer to that question, is the same
as the answer to the question - can the controlsource of a combo be
more than one field.

 

[Douglas J. Steele]

Not as far as I know.

However, I will admit that my knowledge in this area is limited because a)
lookup fields are an abomination that should never have been introduced into
Access (see http://www.mvps.org/access/lookupfields.htm at "The Access Web"
for some of the reasons why) and b) you should never be working directly
with tables.