Solved "Update query" to update Value in one column based upon value of other column in same table

wikihow

Member
Local time
Tomorrow, 02:22
Joined
Apr 14, 2020
Messages
38
Hello Everyone,

I have a table with part of it's data coming from an Append query.
In next step, some of other columns are needed to be filled upon the basis of what is the existing value in other columns of same table.

1. Attaching an example DB, where tbl_ItemWardrobes is partially appended table alongwith the Field "Doors".

2. Now I need to update "PIID_Doors" in same table on the basis of text value in "Doors".

3. Field "Doors" in tbl_ItemWardrobes and field "PartItemName" in tbl_PartItem are text values and we need them to match them for finding "PIID_Doors" in tbl_PartItem>PartItemID.

4. When i can find out a working method, these updates will be made in a continous form with a button to activate update query of selected records.

I have tried update queries but none of them work probably its the same table we are updating.

Can anyone provide me a solution for this?

Many Thanks in advance.
 

Attachments

Code:
UPDATE tbl_ItemWardrobes INNER JOIN tbl_PartItem ON tbl_ItemWardrobes.Doors = tbl_PartItem.PartItemName SET tbl_ItemWardrobes.PIID_DOORS = [tbl_PartItem]![PartItemID]
WHERE (((tbl_ItemWardrobes.Doors) Is Not Null));
 
Thanks a lot, This works. Appreciate for your looking and solving my problem. We created a custom join. I have like 7 fields to update in my update query. I will check at my end, if i can create custom joins for all of these fields or not. Thanks again
 
@wikihow

I am not sure what you are doing exactly, but often when you start duplicating data in various positions, it can point to an incorrectly normalised data structure. The idea is you shouldn't HAVE to repeat the data to understand how the data is related. It should arise naturally out of the table design. eg, In your case, having appended the data, what would the effect be if the original data you copied then got changed subsequently. Would it invalidate the copy?
 
@wikihow

I am not sure what you are doing exactly, but often when you start duplicating data in various positions, it can point to an incorrectly normalised data structure. The idea is you shouldn't HAVE to repeat the data to understand how the data is related. It should arise naturally out of the table design. eg, In your case, having appended the data, what would the effect be if the original data you copied then got changed subsequently. Would it invalidate the copy?
Agree, I gave a solution for this question, keeping in mind it could be to prepare a new table from old info. This is 'a no go' in a live application.
 

Users who are viewing this thread

Back
Top Bottom