Update query does not work - Access 2016

thao-nhi
thao-nhi used Ask the Experts™
on
I tried to update one table based on the value of another table using a query. Fields are present in both tables with the same format.
Primary keys are joined. Nothing worked when I clicked run. What am I missing. Any help is appreciated.
Query.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dirk StraussSenior Full Stack Developer
Distinguished Expert 2017
Commented:
Shouldn't the second table be RepairCost?
Tom CieslikIT Engineer
Distinguished Expert 2017
Commented:
I think you're try to looking for RepairCost field in Work Orders table, but on your picture RepairCost is in RepairCost table.
Maybe this is your issue or  I don't see all fields
Distinguished Expert 2017
Commented:
The query looks fine.  I'm assuming this is a data fix up and you will be deleting the second table.

Create a query that joins the two tables on WO.  Select columns from both tbles  What do you get?

Typically primary keys are autonumbers and you would never join two tables on their autonumber so you might not actually have a valid join.
HainKurtSr. System Analyst
Commented:
try this

UPDATE WorkOrders
INNER JOIN RepairCost ON WorkOrders.WorkOrderID = RepairCost.WO
SET
WorkOrders.RepairCost = RepairCost.RepairCost,
WorkOrders.RepairDesc = RepairCost.RepairDesc;

Open in new window


either create a new query, show sql and copy paste
or update yours with this and run...

Author

Commented:
thank all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial