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!

 

[ranman256]

are both of these AUTONUMBER?
you cant update autonumber. the fields you're updating must be LONG INTEGER.

(you cant set the EQUAL if you're updating the ID field.)

 

[Prevost]

The Table1 values are AUTONUMBER (not updating these values). The Table2 values are LONG INTEGER.

 

[Joe4]

Cross-posted: Updating Field in Table2 with Table1 values based off matching criteria

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Prevost]

Sorry. I have posted the same question at the following sites:

Updating Field in Table2 with Table1 values based off matching criteria

sql - Updating Field in Table2 with Table1 Values based on criteria - Stack Overflow

I will add a comment here if I get the answer from another forum and give that link.

 

[Joe4]

How are you joining your two tables in your Update Query?
Can you post the SQL code of your attempted Update Query (change to SQL View and Copy and Paste code here)?

 

[Prevost]

I am not sure if I am joining my two tables in my Update Query...I clicked the tab 'Create - Query Design - Update'. Then in the table that comes up at the bottom of the screen I enter:

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

Table1 = tblUWFrameInfo
Table2 = tblWoundFrameCosts

UPDATE tblUWFrameInfo INNER JOIN tblWoundFrameCosts ON tblUWFrameInfo.ID = tblWoundFrameCosts.UWFrameID SET tblWoundFrameCosts.UWFrameID = [tblUWFrameInfo].[ID]
WHERE (([tblWoundFrameCosts].[UWFrameID]=[tblUWFrameInfo].[ID]));

Thanks so much. I am also trying to read up on SQL for Access so I know a little more about what I am talking about.

 

[Joe4]

Well, I see a problem with that code. It appears that you are trying to join your two tables on the exact same fields that you are trying to update (set equal) in your Update Query!

Look at the query, as I have formatted it below for easy reading:

UPDATE 
    tblUWFrameInfo 
INNER JOIN 
    tblWoundFrameCosts 
ON 
    tblUWFrameInfo.ID = tblWoundFrameCosts.UWFrameID 
SET 
    tblWoundFrameCosts.UWFrameID = [tblUWFrameInfo].[ID]
WHERE 
    (([tblWoundFrameCosts].[UWFrameID]=[tblUWFrameInfo].[ID]));

Your Join, Set, and Where (criteria) fields are same relations (tblUWFrameInfo.ID = tblWoundFrameCosts.UWFrameID). If they are already equal, there is nothing to be gained by updating fields to be equal (wouldn't be changing anything).

Usually, in an Update query between two tables, you are joining the two tables on some field(s), and then updating other fields.

Perhaps if you post a small data sample of each data table, along with your expected results, it will make more sense exactly what you are trying to do and how the Update Query should be written.

 

[Prevost]

Yes I can do that. I just have to figure out how to attach a file to my post.

 

[Joe4]

You cannot attach files on this forum, however, you can copy and paste images from Tables and Queries (like I just did below). Just highlight all the data in your Table/Query and Copy & Paste (make sure you do not post hundreds of records, just a handful).

1	6/30/2013
2	6/30/2014
3	6/30/2016
4	6/30/2019
5	6/30/2020

<caption> Table4 </caption><thead>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] MyDate [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>