Multi-field JOIN?

[slashbot1]

Hello,

I have two tables, for the sake of simplicty, Archive and New - both have same structure. I need to update Archive with data from New. Sometimes, New will contain data that is already in Archive. We do weekly downloads/imports and often the data overlaps.

Basic Structure for both.
Case
Revision
Suffix

Problem, neither table can have Case as a PK, because Case can be entered multiple times and is only unique when considering BOTH the Revision and Suffix. Basically the PKs for the tables should be those three fields. However, sometimes either Revision or Suffix is NULL and therefore cannot be used as a PK.

I got searching around and have found that SQL has the ability to JOIN on multiple columns, but I have not been able to do this in Access (2007) .

I can easily find those records that exist in New that are not in the Archive, they have unique cases, but if the case already exists in Archive, I am not sure how I can create an UPDATE statement that will only run where Case, Revision and Suffix match.


Any advice either on the SQL or even on an alternate way of addressing this problem?

Thanks in advance,

[slashbot1]

I stepped back and took another look at my problem and solved it. Definitely a case of making it way harder than it needed to be.

I deleted anything in the Archive that matched anything in the New table based on Case, Revision and Suffix. Then I just did a simple Append query for all of the records in New.

Done and done. Always open to suggestion though if anyone has thoughts on this or the process in general. I can imagine it is a likely problem.

My DELETE statement

DELETE *
FROM Archive
WHERE EXISTS
(SELECT * FROM New
WHERE Archive.CaseNumber = New.CaseNumber
AND Archive.Revision = New.Revision
AND Archive.Suffix = New.Suffix);