Forum Discussion
Creating a table that checks matches two values to another table to replace the values
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
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
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 !
5 Replies
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
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.
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
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 !
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.
