If I am understanding correctly, I think I found a solution to a similar situation here:

http://www.informationweek.com/news/software/reviews/showArticle.jhtml?articleID=173601651

Your case differs a little bit though, you will probably have to add a "weight" field to table 1 first and just leave it blank.

Hope this helps and good luck!
Permalink | Report

Use this:
UPDATE Table1, Table2
SET Table1.weight = Table2.weight
WHERE Table1.id = Table2.id;
Permalink | Report

"If you have created multiple tables, then you need a method of telling the program to bring the information back together. To do this in Microsoft Access, you will need to define some relationship rules between the existing tables. After you have done so, you can create all sorts of queries and reports from several tables in the same time. For this, Microsoft Access will match data in key fields (key fields are fields that has the same name in two or more tables). Most of the time, the matched fields are the primary key from one table (this provides a unique identifier for each record) and a foreign key (a key that is present in another field). Creating relationships between tables is easy: go to the database window and click on the Tools option bar which you will find at the top. Next select the Tables that you want to create the relationships between. Click on them and select the Add Button setting. Next, you have to drag the primary key pf the Parent table and drop it into the same field in the Child table. Now select the Enforce Referential Integrity option that you can find in the dialog window below. Below the Enforce Referential Integrity option, you can notice two more settings: Cascade Update Related Fields and Cascade Delete Related Records. If the first one is selected (Cascade Update Related Fields), then when you change a primary key in the primary table, the program will automatically update the matching value in all related records found. If the second option is selected (Cascade Delete Related Records), when you delete a record in the primary table will also delete all the other related records found.

Now in the Relationships dialog window you should see a line connecting the two fields. Using table relationships is very useful for when you are working with multiple tables in order to prevent the duplication of information in a database."
Source(s):
http://www.mstipsandtricks.com/tips-and-tricks/ms-office-tips-and-tricks/ms...

Permalink | Report

You need to make a query. Click on the query tab and pick "new". once the designer window comes up it will ask you what tables you want to add. Pick the 2 tables you are using and add them both. Now you should see both of those tables on the top half of the screen. Drag the unique identifier that you mention from one table to the unique identifier on the other table.

You now have the makings of a query that will get you the data you want. You have a choice to make now. If all you want to do is get data from one to the other then you want to make this into an "update" query. But every time data is added to one table you will need to run the update table again. If you are displaying this information somewhere else, consider just making a "select" query, and treating it like a table. That way any changes in the base tables will show in your query without you having to have run the update.

Here's how to make the "update" query option:
Drag the weight field from the table that currently has blank weights to the grid part, anywhere is fine. Then click on the "query" menu and choose "update". It will ask you which table you want to update, that would be table 1 for you.
Now you should have a new row title on the grid called "update to". Type the name of the field from table2 here. If the names are the same then you will have to type the table name with a dot then the field name all in brackets like this: table2.weight
If you run this then everything that is in table2's weight field will overwrite table1's weight. It sounds like this is not quite what you want so you need to add a criteria. So drag the weight field from table2 down to the grid in the next column. Put the words "not null" in the criteria row under that column. this will make sure that you don't put blanks where there might already be data in table1. Save that query for future use and run it. it will tell you how many records it's going to change, click OK if it looks right.

Here's how to make the "select" option:
Drag all the fields you want to see from the tables onto the grid. Pick weight from table2 and everything else from table1. The advantage to this method is that whatever way you are using to put the data in one table doesn't need to be changed and you don't need to remember to run the update. Just use this saved query as if it was a table, you can export it too.
Permalink | Report

Yeah, that's it. The inner join is the key because it will allow the tables to join even when there is 'missing data' from the other table.

I'd just like to add to the discussion that what you have done seems totally wrong and should never happen in the first place. Having data like that in 2 separate tables is totally contrary to proper database design. You should not be copying data from one table to another like this. You should be using queries to build the views you want, rather than having 2 tables with redundant info.

I'm sorry that you may not find this comment very helpful, but it just seems to me that you've backed yourself into a corner with a problem that should simply not have happened in the first place. I just can't see any justifiable reason why you'd want 2 tables like that. I have to wonder if you need to put access down and go back to studying basic database design.

Microsoft would like you to believe that building a database is no different than making a spreadsheet or powerpoint presentation, and the fact it that it is very different, an art and science in itself. I'd have to wonder if you shouldn't just try to do what you are doing in a spreadsheet.
Permalink | Report

You can use an append query
1. Create a new Query
2. Select the source table by "Show Tables"
3. Change query to an appending query
4. Select the table to be appended
5. map fields
6. run
Source(s):
Microsoft Append Query

Permalink | Report

you can do it in following ways:
1. Do join query
2. Do append query
3. Do a table merge
Permalink | Report