Compare codes between two tables, return non-similar codes + fields

[ironfelix717]

Hi,

Not sure where to begin, queries are my weakness!

I have two tables, one is "Master", other is "Actual". Both have a field "Code". Only ACTUAL has a field "POSITION"

I need to return a list of all items inside ACTUAL where the code in ACTUAL does not equal the code for that same item in MASTER, as well as return it's associated fields ("POSITION") in ACTUAL.

I was able to compare them using the query wizard which resulted in a left join, but that compared them completely, not sure how to add criteria to filter them for results where ActualCode <> MasterCode.

Here is schema info:

1.) MASTER may contain thousands of ItemIDs (speed important) - But the ItemID in master will ALWAYS be unique

2.) ACTUAL may only contain a dozen or two ItemIDs - But may have duplicate IDs with varying codes

3.) There is a possibility that an ItemID in ACTUAL may not exist in MASTER - and I would love a way to identify this occurrence in the resultant query.


Attached is test DB + images of expected results.

Thanks for any help!

[isladogs]

Your return results don't match the data shown

Perhaps this will do what you want:

SELECT ACTUAL.ID, ACTUAL.ItemID, ACTUAL.Code, MASTER.Code, ACTUAL.ItemPosition 
FROM ACTUAL LEFT JOIN MASTER ON ACTUAL.ID = MASTER.ID
WHERE MASTER.Code<>Actual.Code Or MASTER.Code Is Null;

Result:


Calling a field 'code' isn't a good idea as it played havoc with the code tags here!

[ironfelix717]

Isladogs,

Thanks for replying,

My results match the data i provided. Albeit, they are manually generated.
I'm not sure what your results are providing. They do not meet requirements.
Every itemID has a master code, so why yours does not display this information is puzzling

[ironfelix717]

Update:

Solved using a LEFT JOIN.

This displays all items in actual where codes do not match master.
And returns any items in Actual that do not exists in master.

Thanks

CompareCodes_Solved.accdb.zip

[isladogs]

I used your data to generate those query results linking the tables using the ID field following what I understood you wanted.

Your results include ID=7 even though the code values are identical in both tables.
Because the values are identical, my query excludes that value.
I realise the ItemID is different in each table for ID=7. Perhaps that's what you wanted to link the tables on instead?

ID values 9 and 10 do not exist in the master table hence those records are blank.
You asked for that info in point 3 of your first post.

The issue with the Code field was when I uploaded the query sql including e.g. [Actual].[Code] using the site code tags as it confused the forum software.

As my attempt doesn't meet your requirements, I'll leave you to adapt it as you want.

EDIT
Posts crossed. Glad you've solved it for yourself.
As I suspected you wanted to link on ItemID rather than ID. Otherwise same idea as my attempt.