General discussion


Compare records in 2 tables and move data from one table to the other

By Criminey Jicket ·
I have two tables with 3 columns in common: FirstName, LastName, Department. Table A (Access table) has additional columns (RecordID, Address, Phone, etc. This table is missing data in the some of the records in the Department field.

Table B (an Excel spreadsheet) is missing no data in any of the records. How do I compare the records in each table and move the Department data in Table B to the Department column in Table A where it is missing?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

down and dirty but fairly easy fix

by sjs123 In reply to Compare records in 2 tabl ...

In Table A, add the columns missing if they are not already there.

Is Table B a table in access? If not, go up under
file and get external data. A wizard will run you
through the steps.

Just make sure that the tables have primary keys
in common! (The same primary key in each table will take care of the matching of records for you

Once Table B is actually in Access, you can use a
query to get the info from Table B to Table A.

Have you made a query which includes two tables?
If not, first make a basic query with the data
from Table A. Go to design view of this query. Up where the field list for the table
appears, right click. You should see a Show Tables option. Click on it. Add the second table, Table B. Now add the columns to the query from the Table B that you want to add to the Table A. Open the query in datasheet view (click run) and you should see the info from both tables.
The Table A column for department will still have
missing data. Go to the Table B column for department. The info you want to add to Table A should be there. (Also because the primary keys are the same, you should have the same records in each table.) Now you can click on the Table B.Department column heading, do a copy and then paste to the Table A.Department column heading. The info should all transfer. This will transfer everything from the Table B Department column and OVERWRITE the Table A Department column. If there is anything you don't want changed in Table A Department, but only want to fill in the blanks,
first modify your query to only give the the nulls in Table A.Department column.

Once you have everything you need in Table A, you
can delete Table B.

Hope this makes sense and helps.

Related Discussions

Related Forums