appending new records to a table with a compound primary key

[Paul]

I need to append new records on a daily basis from another database to a
linking table that connects a project table (tblProject) with a contact
table (tblContact). The Primary Key of the linking table
(tblProjectContact) consists of two fields - ProjectID and ContactID. I'm
trying to build an append query that appends all records from the source
table that don't already exist in the target table. That is, I need to
append all records from the source table where the combination of ProjectID
and ContactID don't already exist in tblProjectContact.

I would know what to do if there was only one field to check - you creater
an outer join to the target table in the query grid, and set the criteria of
the field in the target table to "Is Null." I'm expect that you can do
something similar with the two fields, but I'm not sure what would work.

Do you create an outer join pointing to the two fields in the target table,
and then define some combination of AND and OR in the Criteria Grid, and if
so, what would the criteria expression(s) look like?

Thanks in advance,

Paul

 

[John W. Vinson]

I need to append new records on a daily basis from another database to a
linking table that connects a project table (tblProject) with a contact
table (tblContact). The Primary Key of the linking table
(tblProjectContact) consists of two fields - ProjectID and ContactID. I'm
trying to build an append query that appends all records from the source
table that don't already exist in the target table. That is, I need to
append all records from the source table where the combination of ProjectID
and ContactID don't already exist in tblProjectContact.

I would know what to do if there was only one field to check - you creater
an outer join to the target table in the query grid, and set the criteria of
the field in the target table to "Is Null." I'm expect that you can do
something similar with the two fields, but I'm not sure what would work.
Yep.

Do you create an outer join pointing to the two fields in the target table,
and then define some combination of AND and OR in the Criteria Grid, and if
so, what would the criteria expression(s) look like?

You'ld just check for either one of the linking fields being NULL: if both are
required (as they should be), the record will either be found and have a value
in both fields, or the record won't exist and both fields will be null.

INSERT INTO tblProjectContact(ProjectID, ContactID)
SELECT ProjectID, ContactID
FROM <sourcetable> LEFT JOIN tblProjectsContacts
ON sourcetable.ProjectID = tblProjectContacts.ProjectID
AND sourcetable.ContactID = tblProjectContacts.ContactID
WHERE tblprojectContacts.ProjectID IS NULL

 

[Paul]

Thanks for the explanation, John.

Question - should the WHERE clause be expanded to include criteria for the
ContactID field as well?

WHERE tblprojectContact.ProjectID IS NULL or tblProjectContact.ContactID IS
NULL

?

 

[John Spencer]

No, that is not necessary because the JOIN clause will exclude any
records where either field is null and therefore the result will be that
the records returned will have both fields as null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for the explanation, John.

Question - should the WHERE clause be expanded to include criteria for the
ContactID field as well?

WHERE tblprojectContact.ProjectID IS NULL or tblProjectContact.ContactID IS
NULL

?

 

[Paul]

John V and John S,

the JOIN clause will exclude any records where either field is null and
the result will be that the records returned will have both fields as null

Then I haven't yet grasped how this works. I only want to exclude those
records where the target table has the same two fields as the source table.
I do want to import all other records; that is, where either field is null,
as well as those where both fields are null. What do I need to do to
accomplish that?

Thanks

Paul

No, that is not necessary because the JOIN clause will exclude any records
where either field is null and therefore the result will be that the
records returned will have both fields as null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Try the query. IF either field OR both fields are null then it should
work as you want.

Make a backup of your date first in case this does not work as you expect.

Or if you want to manually check what records would be added just run
the SELECT portion of the query.

SELECT ProjectID, ContactID
FROM <sourcetable> LEFT JOIN tblProjectsContacts
ON sourcetable.ProjectID = tblProjectContacts.ProjectID
AND sourcetable.ContactID = tblProjectContacts.ContactID
WHERE tblprojectContacts.ProjectID IS NULL

That should show you which records would be added. You can then
manually check if the result includes all the records you want to add or
if there are some missing records or some duplicate records. You might
need one simple addition to the query if there is the possibility of
duplicate combinations of ProjectID and ContactID in the source table.

SELECT DISTINCT ProjectID, ContactID
FROM <sourcetable> LEFT JOIN tblProjectsContacts
ON sourcetable.ProjectID = tblProjectContacts.ProjectID
AND sourcetable.ContactID = tblProjectContacts.ContactID
WHERE tblprojectContacts.ProjectID IS NULL

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Question - should the WHERE clause be expanded to include criteria for the
ContactID field as well?

If your belt is secure, do you need to check your suspenders?

Not needed. They'll either both be NULL or neither one NULL, so the second
check will always give the same answer as the first.

 

[Paul]

Gentlemen,

Well, I tested it and of course you're both right. I put the "Is Null"
criteria in each field and then both fields, and in all three cases the
SELECT statement returns the same number of records.

I find that understanding the finer points of queries is one of the most
challenging part of developing Access applications. In this case, I would
have bet someone a dinner that you had to set the Is Null criteria in an OR
clause for both fields, but I can see that the belt alone is holding the
pants up, without any help from the suspenders.

Thanks so much for your help and guidance with this.

Paul

 

[Paul]

the *non-existence* of a row which matches a row in sourcetable

Great way to put it, Ken - I think you may have just turned on a light in a
dark room for me. If I understand it correctly, it works this way because
tblProjectContact is on the end of an outer join. If it was an inner join,
then I would be looking for a row in which ProductID is null. Is that
right?

I always get a chuckle when you British make fun of our American metaphors.

Thaks so much.

Paul

 

[Paul]

Ok, that makes sense. Thanks for the additional explanation, Ken.

Paul

Paul:

That's exactly it as far as an outer join is concerned. With an inner
join,
though, you wouldn't look for row with Null at all because if a position
in
the foreign key column of the referencing (many-side) table is Null, then
there would be no row returned at all. Inner joins only return a row if
there's a match, outer joins return all rows from one side of the join
regardless of whether there's a match or not.

Ken Sheridan
Stafford, England

the *non-existence* of a row which matches a row in sourcetable

Great way to put it, Ken - I think you may have just turned on a light in
a
dark room for me. If I understand it correctly, it works this way because
tblProjectContact is on the end of an outer join. If it was an inner
join,
then I would be looking for a row in which ProductID is null. Is that
right?

I always get a chuckle when you British make fun of our American
metaphors.

Thaks so much.

Paul

Paul:

[quoted text clipped - 56 lines]

Paul