Importing Data

[OsmondB]

Hi all,

I have two separate databases, one with a FirstName and
LastName for a list of employees, and the second has the
FirstName, LastName and Address for the same employees.
What I am trying to do is find the easiest way to copy the
Address field from the second database into the table with
the FirstName and LastName in the first database while
ensuring that the criteria of FirstName/LastName are
equivalent in both tables (basically making sure the right
address goes to the right employee). I've tried using
Append Queries/Update Queries but I can't seem to get it
right. I'm fairly new to Access and have NO knowledge of
SQL or VB. If anyone could offer any advice it would be
greatly appreciated. Thanks!

 

[Nikos Yannacopoulos]

In your target database (the one you want to add the data into), link the
table in the source one (File > Get External Data > Link). Then opebn the
target table in design view and add the additional field(s).
Next, make a new query in design view, add the target tabkle as well as the
linked table in the source db and join on the two common fields; this will
ensure you match records correctly. This done, changing the query to an
update one will do the job.
When done, just delete the linked table icon in the target database (this
will not affect it in the source one).

HTH,
Nikos

 

[OsmondB]

I've got the table linked from the source db and the query
created with the target table and source table added, and I
added the fields from both tables to the query, but I'm not
sure what you mean when you say "join on the two common
fields...changing the query to an update one will do the
job." Sorry, I'm a newbie to Access

 

[OsmondB]

Thanks Nikos for you help! You can disregard my last post
about not understanding the query criteria etc...I figured
it out and it worked perfectly. Greatly appreciated!

 

[Nikos Yannacopoulos]

While in query design view with the two tables there, drag and drop the
firstname field from one table in the upper part of the screen, to the
equivalent field on the other; they will be connected with a line, that
represents a join. Do the same with the lastname field. This forces access
to match records in the two tables where both fields have the same value in
the two tables. Switch to datasheet view temporarlily to view the result and
verify it returns the records matched correctly.
Now back to design view, go to menu Query > Update, to change your query
type from Select to Update. Notice the new line headed Update To: that
appears in the grid under the field names; in that line under the address
field of the target table, type:
[NameOfLinkedTable].[AddressFieldName]
and repeat for all address fields if more that one (use the correct names).
Now you have told Access what to update the empty fields to.
Finally, go to menu Query > Run. If everything is OK, a few seconds later
you will be prompted to accept the updates.

HTH,
Nikos