Unmatched query with multiple fields

wynstonh · Nov 11, 2016

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 · Nov 11, 2016

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 · Nov 11, 2016

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 · Nov 11, 2016

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 · Nov 11, 2016

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 · Nov 11, 2016

Ah, my mistake, from clause is not complete

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

wynstonh · Nov 11, 2016

perfect!!

thanks so much

Unmatched query with multiple fields

wynstonh

Registered User.

Attachments

CJ_London

Super Moderator

wynstonh

Registered User.

CJ_London

Super Moderator

wynstonh

Registered User.

CJ_London

Super Moderator

wynstonh

Registered User.

Similar threads

Users who are viewing this thread