Creating a table that checks matches two values to another table to replace the values

[Jsaidtecnimont]

Ok so I am having trouble with a connection between two tables where I would like to replace the "Object Code" in the big data table with the "New Object Codes" based on the original "Object Code" but they are also based on the "Project" and I found it easier to create a new query that has the three columns: "Project", "Old Object Code", "New Object Code". This is all based on the Object Type, some will be changed and others not.

Example of First Query:

Project	Object Code	Object Type	Desc	Units	Weight
1	A	Underground
1	B	Substation
2	A	Underground

Example of New Query:

Project	Old Object Code	New Object Code
1	A	Q
2	A	F

So I would want to replace all the Object Codes in the first query with the new object code, but as you can see it is based on both the project and the old object code and isn't necissarily the same across the projects. Also it is limited only to the "underground" object type only that needs to be changed. Is there a way where I can create this double check?

Thanks in advance

[BA_Pete · Accepted solution]

Hi @Jsaidtecnimont ,

I think the simplest way to go about this would be to just Left Outer merge your New Query (NQ) onto your First Query (FQ) on FQ[Project] and FQ[Object Code] = NQ[Project] and NQ[Old Object Code].

You can do a two-field merge by using Ctrl+click on the fields in the merge dialog. Make sure to Ctrl+click the fields in the same order on each table:

Pete

[serpiva64 · Accepted solution]

Hi,

if i understood you want to get in the first table the new object code

you need to merge the first query this way

then expand this way 

and you get this

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Jsaidtecnimont]

Thank you for your response! Unfortunately, I left out the important part of having some elements checked while others not based on object type. I re-edited the question. Sorry for the mistake.

[BA_Pete · Accepted solution]

Hi @Jsaidtecnimont ,

I think the simplest way to go about this would be to just Left Outer merge your New Query (NQ) onto your First Query (FQ) on FQ[Project] and FQ[Object Code] = NQ[Project] and NQ[Old Object Code].

You can do a two-field merge by using Ctrl+click on the fields in the merge dialog. Make sure to Ctrl+click the fields in the same order on each table:

Pete

[Jsaidtecnimont]

That's great! Thank you for the quick response, but I realized I forgot to mention, not all new object codes should be changed that's why I'm not merging. I have another column for example that states object type, and only a specific type of object needs new object codes, does that make sense?

I will edit the original question, sorry for the missing information.

[serpiva64]

I think it is not a problem. Once you have merged you create a conditional column that gets the new or the old object code based on your type of object