Two tables, different structures, can Access 2003 update or append?

  • Thread starter Thread starter Brian Goodheim
  • Start date Start date
B

Brian Goodheim

I have two tables with similar but not exact table structures and I want to
update the successor table with records from the old table and then discard
the old table. I will only need to do this once. Does Access have a field
mapping dialog to help in this process or do I need to write a program to
manipulate the data?

Thanks,

-Brian
 
When you set up the query, you can specify the field to use for the source
in the destination field.

HTH;

Amy
 
Hi Brian,

To tag on to what Amy told you...

~~~~~~~~~~~~ UPDATE

make a new query and include both of your tables -- link
them on the common field(s) if the data types are the same;
if not, you can use criteria to make sure the records from
each table match

Then, put the fields on the grid that you want to update

change your query into an UPDATE query

from the menu --> Query, Update

this adds another row to the grid
Update To-->

At this point, it would be good to give your tables an
ALIAS, which is typically a short name or abbreviation

right-click anywhere and choose Properties from the shortcut
menu

click on the first table and type
A
and press the ENTER key in the Alias property

assign the second table an Alias of B
(or whatever you like)

then, in the UpdateTo cell under each field
Tablename.Fieldname

where you would substitute your Alias for Tablename

~~~~~~~~~~~~APPEND

To Append, you would need to link your tables so you can
find the records that are in Table1 but not in Table2
Right-click to Edit the link line to show all records from
Table1

Change the Query to an Append Query
this will add another row to the grid
Append To -->

from the menu --> Query, Append --> choose the tablename you
want to add records to (Table2)

put all the fields from Table1 on the grid
fill out the Append To cell under each

On the grid, put the PrimaryKey field of Table2
In the criteria cell --> Is Null
Append To --> leave blank

~~~~~~~~~~~

to Run the Query, click the ! button or choose Query, Run
from the menu
Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top