query with multiple primary keys

[Harlan]

I have two tables with a relationship betweet them using a two field primary
key. I want to use a query to return all values from table a, but only those
from table b that are the same. when using the query wizard, it will only
allow setting ONE of the primary key fields to allow this, but naturally,
the returned information isn't correct as it isn't looking at the other
field... what to do?

 

[tina]

just link the second pair of key fields in the query design window directly.
the wizard is a handy "shortcut" to get the ball rolling at times (just like
the form and report wizards), but only a start. don't be afraid to tweak the
results to get what you want.

hth

 

[Harlan]

That is what I was trying to do....
I linked BOTH key fields from table a to table b. Then clicked on the links
one at a time to change them to allow "all from a, and equal from b".... BUT
it will only allow you to do this to ONE of the links at a time. You get an
error message if you try to run the query with BOTH set up that way.????

 

[Smartin]

That is what I was trying to do....
I linked BOTH key fields from table a to table b. Then clicked on the links
one at a time to change them to allow "all from a, and equal from b".... BUT
it will only allow you to do this to ONE of the links at a time. You get an
error message if you try to run the query with BOTH set up that way.????

I think what Tina was trying to express was start with what Access will
allow in the query design view, then switch to SQL view and build up
from there.

Try starting with one join, make it a left join, then switch to SQL view
and modify it:

start with
Table1 LEFT JOIN Table2 ON Table1.Key1 = Table2.Key1

change to
Table1 LEFT JOIN Table2 ON (Table1.Key1 = Table2.Key1 And Table1.Key2 =
Table2.Key2)

 

[David F Cox]

One approach is to join on one of the keys and have a WHERE clause on the
other.
e.g. WHERE [order.key] = [orderline.orderkey] OR ISNULL([order.key])

Another approach is to concatenate the keys.(carefully)

 

[tina]

no, actually i expressed exactly what i meant. you can link multi-field
primarykey/foreignkey pairs between tables, directly in query design view
(and in SQL view too, of course). and set a Left Join on the link between
each pair of fields. i had no problem doing it without using the SQL view
pane, in my test where Table3 is the parent table with a two-field key of
firstname and lastname, and Table4 is the child table with a matching
two-field foreign key of firstname and lastname, as

SELECT Table3.firstname, Table3.lastname, Table4.anotherfield
FROM Table3 LEFT JOIN Table4 ON (Table3.lastname = Table4.lastname) AND
(Table3.firstname = Table4.firstname);

i'm posting the SQL, of course, but i created the query solely in query
design view. Harlan, if Access is refusing to allow you to set the join on
more than one pair, perhaps there is a problem with the *table*
relationships - suggest you review those for possible errors.

hth

 

[Harlan]

Thanks all.... I figured it out with your help. Tina is correct, with the
info I gave her. I didn't realize that my table b ALSO was left joined with
table c.... adds complications.

thanks again for the fast response.
Harlan

 

[tina]

you're welcome