Update Query between 2 tables

[Prevost]

Hi There,

I am trying to run an update query using two tables, Table1 and Table2. I am updating a new field in Table2 to the correct ID number from Table1, using an existing field from Table2 (which also exists in Table1, the reason for just trying to get the ID number from Table1 and avoid redundant fields). I selected the Query Design and then selected an Update Query. I have both tables in the window. In the table where I enter the criteria for the update, I was thinking this would work (but doesn't)...

Field: UWFrameID
Table: Table2
Update To: Table1.ID
Criteria: [Table2].[UWFrameID] = [Table1].[ID]
or:

where UWFrameID is the field that I want to change in Table2 and I am trying to update it to the value from Table1.ID using the criteria that the existing field from Table2 matches the corresponding Table1 value.

Thanks in advance for any help/suggestions!

 

[Prevost]

Here is the table containing the frame information
http://i61.tinypic.com/vq1oas.jpg

Here is the table containing the configurations
http://i57.tinypic.com/e02ypj.png

Here is the updated configuration table (that contains 4 and 10 in the UWFrameID field, which are the correct corresponding numbers)
http://i57.tinypic.com/2u8l0qw.png

 

[Prevost]

Please let me know if this is sufficient!

 

[Joe4]

Yes, that helps immensely. You want to join your two tables on the FrameNo field. So your SQL code should look something like this:

UPDATE 
    tblUWFrameInfo 
INNER JOIN 
    tblWoundFrameCosts 
ON 
    tblUWFrameInfo.UWFrameNo = tblWoundFrameCosts.FrameNo 
SET 
    tblWoundFrameCosts.UWFrameID = [tblUWFrameInfo]![ID];

 

[Prevost]

Thank you so so much! That is exactly what I needed! That makes me happy. Okay, so now I just need some explanation on that code please. I am trying to learn some basic SQL so that I can understand it better (right now I am just starting off) and then I will have a better idea of what to ask!

Thanks again!

 

[Joe4]

I think it may be easier to understand (at least initially), in looking at it from the Query Builder standpoint.

First, in the Query Builder you added your two tables.
Now, you need to create a relationship between the two tables. That is, how does it match up the records from one table to the other, so it knows which records to update with which values? So, typically, you are looking for a common populated field to relate the two tables on. Since the ID field is blank, we cannot use that (that is the one we are trying to update!). But we see that we can relate the two tables on the FrameNo field. So, we drag a line from the FrameNo field in one table to the FrameNo field in the other table.

Note, if you have pre-defined relationships set up in your database, it may be automatically trying to relate the two tables on the ID field. If so, you will want to remove that relationship for the purposes of this Update Query. If you see a line automatically connecting the ID fields in these two tables, click on it and hit Delete to remove it.

Now that the relationship is set, you add the field you want to update to the query (the ID field in your second table), and then in the Update To field, you populate it with the field providing the value (the ID field from the first table). Then just run the query.

The SQL code is just the SQL representation of the Query you just built using the Query Builder. Some experienced programmers prefer to write the SQL code directly, skipping over using the Query Builder, but it is not necessary in this case (some things, like Union Queries, cannot be written using the Query Builder and need to be written in SQL View).

Does this help?

 

[Prevost]

Yes that does help thank you. I had an existing relationship between the tblUWFrameInfo.ID to tblWoundFrameCosts.UWFrameID when I should have had the relationship exist that you mentioned above. I then tried that same Update Query on a copy of the database without using the SQL you provided and it worked so I am a little further ahead now (although I would like to write all the queries in SQL until I have a solid understanding and then I can use the shortcuts available). Thanks for all your time and help!

 

[Joe4]

Actually, if you are interested in learning to write the SQL code, a good way of doing that is to create the query using the Query Builder, and then switching to SQL View and inspecting the code that it builds. Then you can see exactly what it is doing.

My only complaint is that the SQL code in SQL View is not formatted nicely. It is all kind of jumbled together. I typically copy it out to a Text Editor, or Word, or SQL Management Studio and format it (carriage returns, indents) so it is easier to read and follow.