Unmatched query with multiple fields

[wynstonh]

Hi,

I have 2 tables with matching field names etc, let's call them tbl_archive & tbl_update.
tbl_update can contain either entirely new records (don't appear in tbl_archive) or updates to records existing in tbl_archive.

Capturing the new records is easy with an unmatched query as there is a reference number field which I can use that will only exist in tbl_update for new records and will not exist in tbl_archive.

But what about records that do exist in tbl_archive but tbl_update has 1 or more field changes.
In the example below I would want to see the record from tbl_update as there has been a change to fields 3 & 6.




Any help would be much appreciated....

 

[CJ_London]

you will need a query to link on your reference number field then have multiple OR in the criteria comparing each field

SELECT U.*
FROM TblUpdate U INNER JOIN tblArchive A
WHERE U.fld1<>A.Fld1 OR U.fld2<>A.Fld2......

Convert to an update or append query as required

 

[wynstonh]

Thanks for the response.

Looks good but I'm getting "syntax error in FROM clause".

Got to admit I'm not particularly used to SQL so may just be me being an idiot.

I've got:

SELECT U.*
FROM tbl_update U INNER JOIN tbl_archive A
WHERE U.Status<>A.Status OR U.Offer/Contract end date <> A.Offer/Contract end date

 

[CJ_London]

you have non alphanumeric characters in your field name - Offer/Contract - always a bad idea and can cause misleading errors

trying putting square brackets round the field names, it usually solves the problem

 

[wynstonh]

good point but doesn't solve it

SELECT U.*
FROM tbl_zorinst1 U INNER JOIN tbl_zorinst A
WHERE U.[Status]<>A.[Status] OR U.[Offer/Contract end date] <> A.[Offer/Contract end date]

still getting the same syntax error and I get the same if I remove the OR criteria, just using [Status]

 

[CJ_London]

Ah, my mistake, from clause is not complete

FROM tbl_update U INNER JOIN tbl_archive A ON U.Refno=A.Refno

 

[wynstonh]

perfect!!

thanks so much