Taras
asked on
Update Query from same table
In MS Access I need to update two records(ProductID = 112,113) from third record( ProductID = 111) in the same table.
e.g table tblProduct.
Product_ID Color Manufacture ImportedYesNo Product Group
111 Black Domestic No AA
112
113
114 White Foreign Yes BB
115 White Domestic No BB
e.g table tblProduct.
Product_ID Color Manufacture ImportedYesNo Product Group
111 Black Domestic No AA
112
113
114 White Foreign Yes BB
115 White Domestic No BB
ASKER
Sorry I did not put question properly. Values for records which need to be updated and record which is used for update are in different tables.
So table Product is the same but there are next two tables.
This is table that has value of Product ID’s which will be updated in table tblProduct.
tlbRecordsToBeUpdated(Only one filed with two records for update)
ProductID
112
113
This is table that has the record from table tblProduct , the record that will be used for update.
TblRecordUsedForUpdate (only one record)
Product_ID Color Manufacture ImportedYesNo Product Group
111 Black Domestic No AA
So table Product is the same but there are next two tables.
This is table that has value of Product ID’s which will be updated in table tblProduct.
tlbRecordsToBeUpdated(Only
ProductID
112
113
This is table that has the record from table tblProduct , the record that will be used for update.
TblRecordUsedForUpdate (only one record)
Product_ID Color Manufacture ImportedYesNo Product Group
111 Black Domestic No AA
Whether you are joining a table to itself or to another table, the syntax is the same. Just replace the second instance of the first table name to the name of the second table.
The larger question is what is the common field? Surely you are not just updating two specific records. Given that, the suggested solution won't be much help. I don't see anything in record 111 that would link it to records 112 and 113 in the other table.
The larger question is what is the common field? Surely you are not just updating two specific records. Given that, the suggested solution won't be much help. I don't see anything in record 111 that would link it to records 112 and 113 in the other table.
It would be helpful to see a screen shot of the Relationships diagram (if you haven't made any links, that would be a problem).
ASKER
Part Hartman.
There are three tables not two.
How they are related?
Table tblProduct has Product ID that is related to TblRecordUsedForUpdate .ProductID.
And it has all records in.
Table tlbRecordsToBeUpdated has only records ProductID's that need to be updated and those ProudutID' are in TblProduct too.
It means three tables One where All records exist.
And two other. One of those two has and always will have only one complete record which is identical as that in tblProduct and is used for update and Second table has only values of ProductID' that has to be updated in tblProduct.
Do not if this help.
There are three tables not two.
How they are related?
Table tblProduct has Product ID that is related to TblRecordUsedForUpdate .ProductID.
And it has all records in.
Table tlbRecordsToBeUpdated has only records ProductID's that need to be updated and those ProudutID' are in TblProduct too.
It means three tables One where All records exist.
And two other. One of those two has and always will have only one complete record which is identical as that in tblProduct and is used for update and Second table has only values of ProductID' that has to be updated in tblProduct.
Do not if this help.
ASKER
PatHartman, apologize for name misspelling.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pat.
Thanks for your suggestion, in broader context this table, tblRecordsToBe updated has records that belong to a specific group. In that specific group belongs record in table tblRecordUsedForUpdate used for update.
You are right in first part of your answer.Table “tblRecordsToBeUpdated” only has role to identify those records which will be updated in tblProucts.
You missed one small thing but very important, that table had just one field.
I added another field named Link_ProductID (it has the same value as s ProductID in tblRecordUsedForUpdate) and it is same for all records in table.
That field is used for connection with tblProduct, otherwise without that field your second part of answer would not be possible.
Thanks for your suggestion, in broader context this table, tblRecordsToBe updated has records that belong to a specific group. In that specific group belongs record in table tblRecordUsedForUpdate used for update.
You are right in first part of your answer.Table “tblRecordsToBeUpdated” only has role to identify those records which will be updated in tblProucts.
You missed one small thing but very important, that table had just one field.
I added another field named Link_ProductID (it has the same value as s ProductID in tblRecordUsedForUpdate) and it is same for all records in table.
That field is used for connection with tblProduct, otherwise without that field your second part of answer would not be possible.
http://www.techonthenet.com/access/queries/update2.php
Or better, see the comment on 2009-09-16 from our top DB Guru:
https://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
So ...
Open in new window