Finding not matching on multiple fields

[Kramnation]

Hi all,

I’m trying to append any new data from one table to a table in my Db. I will run this query on a timer and would like to append any new data from the source table. The source table has no primary key so having a hard time using the not matching wizard (only can select 1 field as Is Null).

Using my table example below, how would I append (Insert) the 2 records that are missing?

I’m using this code, but it will miss the 3rd record

W00001 0001 0003 In Office

INSERT INTO tbJOB_DTL_TEXT_hold ( JOB, SEQUENCE, KEY_SEQUENCE, [TEXT], JOB, SEQUENCE, KEY_SEQUENCE )
SELECT JOB_DTL_TEXT.JOB, JOB_DTL_TEXT.SEQUENCE, JOB_DTL_TEXT.KEY_SEQUENCE, JOB_DTL_TEXT.TEXT, tbJOB_DTL_TEXT_hold.JOB, tbJOB_DTL_TEXT_hold.SEQUENCE, tbJOB_DTL_TEXT_hold.KEY_SEQUENCE
FROM JOB_DTL_TEXT LEFT JOIN tbJOB_DTL_TEXT_hold ON JOB_DTL_TEXT.[JOB] = tbJOB_DTL_TEXT_hold.[JOB]
WHERE (((tbJOB_DTL_TEXT_hold.JOB) Is Null) AND ((tbJOB_DTL_TEXT_hold.SEQUENCE) Is Null) AND ((tbJOB_DTL_TEXT_hold.KEY_SEQUENCE) Is Null));

Source Table

Job Sequence Key Sequence Text
W00001 0001 0001 Error on
W00001 0001 0002 PC located
W00001 0001 0003 In Office


W00002 0001 0001 Passed
W00003 0001 0001 Passed
W00003 0002 0001 Passed
W00004 0001 0001 Passed


My table

Job Sequence Key Sequence Text
W00001 0001 0001 Error on
W00001 0001 0002 PC located

W00002 0001 0001 Passed
W00003 0001 0001 Passed
W00003 0002 0001 Passed

[NTC]

a multi field no-match is challenging. because you are only dealing with 4 fields you should be able to do this:
make a query of the table and include a new calculated field that puts all the data together i.e. All: [Job] & [Sequence] & [Key Sequence] & [Text]

do that for both tables so you have 2 saved queries....

then make your no match query using these 2 saved queries with the join on the All field

I think that will work...

[Kramnation]

Very cleaver, works great! I appreciate the help.