Getting data from one table to another.

[jjohnson88]

I have two tables in a database, TableA and TableB. TableA has a bunch of fields with one being Part Number and one being Rev. The Rev column does not contain information at this time. TableB just has Part Number and Rev. I want to match the Part Numbers in both tables and then copy the Rev for the matched Part Numbers into the Rev column in TableA. What is the easiest way to do this? I would copy and paste but there's 100,000+ records. I tried linking tables and doing an Append query but I don't really know how linking and Append queries work so it didn't turn out too well. Thanks guys!!

 

[dkinley]

This could get long and involved but since you gave us an easy way out by saying you're not really sure at linking ...

Here is a link that may be best beneficial.

http://www.techonthenet.com/access/queries/index.php

What you wanna look for further down the page are queries that talk about left-joins. This should steer you in the right direction and if you have trouble, it will allow anyone here to better understand what you tried and be able to help you tweak it to where it needs to be instead of trying to start from scratch. By all means post back here if you any problems.

-dK

 

[jjohnson88]

Ok. I got that working. I can now see what I need to move and where. But how do I go about moving it? I can't copy the column and paste because for one, there's too many records and two, it'd be in the query but not the table. I need the results from the query to be applied to TableA. I need the Part Numbers that have Revs to have the correct Rev placed in the Rev column in TableA. Does that make sense? Thanks!!

 

[dkinley]

Good to go! Now, for the next step, I think I know what you are getting at ....

Now that you have that down, you know want to look at an update query. This allows you to update a field for all of the records. Basically, you use the left-join to get the data organized like you want and use that structure in an update query. The link will have an example of this.

Failing that, you can use your existing query in a make table query and then turn-around and use the newly made table as a replacement to your first table.

As a last note, be sure you practice the update on a copied table or in a copied database first.

This is the last step in getting what you want, post back if you do not have success.

-dK