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

wikihow

Member
Local time
Today, 13:20
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

  • Database3.accdb
    672 KB · Views: 546

Rene vK

Member
Local time
Today, 08:50
Joined
Mar 3, 2013
Messages
123
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));
 

wikihow

Member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
38
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
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Sep 12, 2006
Messages
15,614
@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?
 

Rene vK

Member
Local time
Today, 08:50
Joined
Mar 3, 2013
Messages
123
@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

Top Bottom