update field from another table

[Robbie]

I have two tables that are nearly identical, table 1 has 2 fields
ISBN10 and ISBN13

table 2 has the same fields, but field ISBN10 in table 1 is empty.

I'd like to update ISBN10 on table 1 but I don't know how to do so. each
ISBN13 is a unique number so between table 1 and 2 there is a way to match
them up, I just don't know how.

 

[Larry Linson]

I have two tables that are nearly identical, table
1 has 2 fields ISBN10 and ISBN13

table 2 has the same fields, but field ISBN10 in
table 1 is empty.

I'd like to update ISBN10 on table 1 but I don't
know how to do so. each ISBN13 is a unique
number so between table 1 and 2 there is a way
to match them up, I just don't know how.

If the tables are in the same database, then almost certainly, you should
not be storing redundant data in two tables... that is, if ISBN serves as a
foreign key to join the tables in a query, you should use a query and obtain
ISBN10 from table two... removing the field from table 1 entirely.
Relational database design principles aren't just to eliminate duplicate
data, redundant data can (and, likely, eventually WILL) cause
difficult-to-resolve logical issues: in this case, when records in table 1
and table 2 both have the same ISBN13, and should, thus, have the same
ISBN10 values, but do not, which one is the "authoritative source" and which
the error? In RDB terms, this is data "normalization" or "normalized"
data...

If the two tables are in different databases, and this is just a one-time
effort to populate the table in the other database, but the databases will
then not be used together, you can link the tables using Get External Data |
Link, and update the ISBN10 field in table 1 from the corresponding field in
table 2 by converting your Query to an update Query. Then you can delete the
link.

Larry Linson
Microsoft Office Access MVP

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The SQL statement would look like

UPDATE Table1 INNER JOIN Table2
ON Table1.ISBN13 = Table2.ISBN13
SET Table1.ISBN10 = [Table2].[ISBN10]
WHERE Table1.ISBN10 is Null

In query design view you should be able to build that as follows
-- add both table
-- drag from ISBN13 to ISBN13 to set up the join
-- Add Table1.ISBN10 to the field list
-- SELECT Query: UPdate from the menu
-- Set criteria under ISBN10 to Is Null
-- Enter the following into the Update To under ISBN10 (brackets are REQUIRED)
[Table2].[ISBN10]
-- SELECT Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Robbie]

Larry, the update is a one time gig, from a table that I am linking.

John thanks a lot for the detailed instructions. I haven't used access like
this so long I forgot that you could simply drag fields between tables to
join. It's so simple.

I first made a test backup and then ran the query on the test. Everything
worked great. Time to make a backup and do it on the real deal.