Compare different data tables for similarities

[kriemer]

Don't even know where to begin with looking for a solution, so here goes with the explanation of the problem...

Daily, I receive 3 lists of data (Item, Item1, and Item2) which imported into 3 Access Tables
• Item Table is a complete (Master) list against which the other lists are compared
• Item1 Table is a partial list based on X criteria
• Item2 Table is a partial list based on Y criteria

I need to compare the Item list with the partial lists and where there is an Item/Item1 match tag that record X (in the Item Table)

Similarly where there is an Item/Item2 match that record will be tagged Y (in the Item Table)

Where there are no matches each record in the Item Table will be tagged Z

I hope this is clear.


Thanks as always

k

 

[FoFa]

Update (ITEM LEFT JOIN ITEM1 I1 ON ITEM.PK = I1.PK) LEFT JOIN ITEM2 I2 ON ITEM.PK = I2.PK
SET ItemFlag = IIF(ISNULL(I1.[PK]) AND ISNULL(I2.[PK]),"Z",IIF(ISNULL(I1.[PK]),"X",IIF(ISNULL(I2.[PK]),"Y","B")))

Basically, might have syntax errors

 

[workmad3]

Is there any particular reason why you have the data repeated like this? The standard idea is to have the master list, and do away with the partial lists, as there are plenty of ways to query the master list for the information you require (for example "SELECT * FROM Items WHERE Y" where Y is your criteria). Using seperate lists like this is just crying out for data anomalies. If there is no way you can change the structure to just have a master list though, then FoFa is going along the right lines.

 

[kriemer]

Stuck with 3 lists

Workmad3:

I am a prisioner of history. I understand your point and I am taking the 3 data lists creating the Master List and from here on only working with the Master List.

FoFa:

Perfect!! Join was the command I was looking for.


Thanks to you both.

k