New field & populate

[Jeff Lane]

I have a large table with an existing field called REGNO and a new table,
imported from Excel, with REGNO and AREA.
I wish to add a new field to the first table called AREA and delete the
second table completely. Then I can allow the operator to update or populate
the AREA field from an existing form by adding an input box.
The second table is already 75% populated. How do I get the AREA details
moved across and ensure that the information still relates to the REGNO
fields.
I looked at using an append query and couldn't understand it, I have also
thought of completely recreating the original table in Excel by exporting
and importing the data but that seems like hard work. I am sure I am missing
something.
There are almost 2000 records in the AREA field so I do not want to have to
type them in.
Please could anyone help?
Thanks

Jeff Lane

 

[Jeff Boyce]

Jeff

Look into an "Update" query. If I understand, you have a list of REGNO's in
Access (and in Excel), and want to have the Access REGNOs associated with an
AREA value that is already associated in Excel.

One approach would be to import the Excel data into a temp table, open your
Access table in design mode and add the AREA field, then create an update
query, where REGNO (current Access table) matches REGNO (imported Excel
data).

Good luck

Jeff Boyce
<Access MVP>

 

[Jeff Lane]

That worked,
thanks

 

[John Vinson]

The second table is already 75% populated. How do I get the AREA details
moved across and ensure that the information still relates to the REGNO
fields.

Be sure that the Regno is the Primary Key (or otherwise has a unique
Index) in your main table.

Create a Query joining the two tables by Regno and change it to an
Update query. Put a criterion on the target table's AREA field of

IS NULL

to be sure you won't overwrite existing AREA records (unless you want
to discard them and replace them with the imported values).

Change the query to an Update query and update the destination table's
AREA field to

[Sourcetable].[AREA]

using your own table name of course.