Joins on compound primary keys

[Jim Shaw]

BlankUsing the query Design Grid, how does one specify a join on two tables
where the Primary Keys consist of a column with a Foreign Key and a column
with a date? MUST I go to the SQL level to do this?

Thanks

-Jim

 

[Allen Browne]

You have 2 tables in the upper pane of query design view.

Drag the number field (foreign key) from one table onto the number field
(primary key) of the other table. Access creates a join line.

Drag the date field from one table onto the date field of the other table.
Access creates the second join line.

Please note that this will not work well if the fields have a time component
as well as the date - e.g. because the Default Value was set to =Now() when
it should have been =Date().

To create a relation between the two tables so you automatically get the
join when you create queries in the future, choose Relationships from the
Tools menu.

 

[Jim Shaw]

Allen: Thanks

I was sure I'd get a Cartesian Product result, but it worked as an inner
join just right.

Problem is that it only works for Inner Joins because I get two sets of Join
Properties to manage.
When I want a Left or Right Outer Join on compound keys, I have to set up a
subquery to resolve the ambiguity. I was hoping to avoid this by finding a
way to have a single join property specification across the entire compound
key.

-Jim

 

[Allen Browne]

To change a 2-key join to an outer join in the query design window, you have
to click both the lines, and make the same choice for each of them.

Once you have an outer join, you may need to follow through for any further
tables that join outwards from that one, and make them outer joins in the
same direction.