Merge data from smaller table into larger table

  • Thread starter Thread starter Taz
  • Start date Start date
T

Taz

Hello Group,

I have a large table (tablemain) with numerous fields and a primary key
named (keynumber). Some fields were originally created with the anticipation
of populating them with data later. Later is now. For example I have a field
named street address in (tablemain). There are around 500 records and of
those, only 10% or so have data in the street address field. I have another
table created by an external query of another database called (tablesmall)
that uses the same primary key (keynumber) as (tablemain) and has the street
address field complete for each record. I need to populate the street
address field of (tablemain) using (tablesmall's) data. I can't simply copy
the field over because it then wipes out any changes or existing data in
(tablemain). How can this be done? I though I might be able to do it with
some sort of update query but it has not worked. Keep in mind I am a novice
so I don't know any scripting. A non-script solution would be greatly
appreciated.

Thank you in advance.
 
Taz,

Your own suggestion of an Update Query is the way I would go. First of
all, make sure you have a backup copy of your database. Make a query
including both tablemain and tablesmall, suitably joined on the
keynumber field from each. If you are using the query design view,
place the street address field from tablemain in the query design grid.
Make it an Update Query (select Update from the Query menu), and in
the Update To row of the street address column in the grid, put...
[tablesmall].[street address]
If you want existing street address data in tablemain to be preserved,
put Is Null in the criteria. Run the query... hey presto!
 
Back
Top