Update query using two tables

[mafhobb]

I have a table called Calls which has several fields, among them one called CallID and another one called IssueType.

Then, there is another table called ReturnInfo with several fields, one also called CallID and another one called Warranty.

I need to come up with an Update query that finds all the CallID with an IssueType of "9" in the table Calls and then use that info to update the warranty field to "1" in the ReturnInfo table with the same CallID.

If the fields were in the same table then I would use an Update query like this one:

        CurrentDb.Execute _
        "UPDATE ReturnInfo " & _
        "SET Warranty = 1 " & _
        "WHERE IssueType = 9", dbFailOnError

How do I do the same thing when the info is on separate tables?

Mafhobb

 

[isladogs]

If you are unsure of the SQL, first do it using the query designer.
Add both tables to the designer and link them using CallID field.
Add both fields for the table you want to upate
Add criterion 9 to the issuetype field.
Now change to Update query and enter the value 1 in the warranty field.

Change to SQL view and paste the code into the VBE

 

[mafhobb]

I think that this might do it

    CurrentDb.Execute _
    "UPDATE Calls INNER JOIN ReturnInfo ON Calls.CallID = ReturnInfo.CallID " & _
    "SET ReturnInfo.Warranty = 1 " & _
    "WHERE (((Calls.IssueTypeID)=9))", dbFailOnError

mafhobb

 

[isladogs]

That's correct


Sent from my iPhone using Tapatalk