How to select the complement of two tables with composite primary keys

[desgordon]

Hi Ms Access experts,

I have two tables that have identical schema and one table has a subset of the data that exist in the other. I want to select the data from the first table (table1 say) that does not exist in the other (table 2) using a query of the form:

select * from table1 where Primary_key_field not in (select primary_key_field from table2)

My problem is that primary_key_field is not a single field but is a combination of three other fields.

My question is whether in MS Access it is possible to do the query using a composite primary key field.

 

[plog]

You need to do a LEFT INNER JOIN (http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join).

Bring in both your tables in Design view, putting table 1 on the left and table 2 on the right.
Link the 3 fields that should match in each table.
Right click on every link you made and select 'Join Properties'.
In the dialog that pops up choose the one that says something like 'Select all records from Table 1 and just those with matching data in Table 2'.
From Table 2 bring in each of the 3 fields down into the query and in the criteria field put 'Null' in each one.
Bring down every field from Table 1 and run the query to produce all the data in Table 1 that isn't in Table 2.