Replace "A" with "N," "B" with "O," "C" with "S," "D" with "T," "E" with "U," etc.

  • Thread starter Thread starter KitKat
  • Start date Start date
K

KitKat

I am embarrassed that I can't remember for the life of me how to do
this.


I'm merging a couple of ".mdb" databases that an MP3 cataloguing
created. I made catalogues on several computers (stupid!) and there's
no good way to merge them but by hand.

I started with WorkDatabase.mdb and HomeDatabase.mdb . WorkDatabase
is already bigger, so I'll be copying values from HomeDatabase into
WorkDatabase.

First of all, there's a table in each called "Artists." Its leftmost
field is "ID," which is an automatically generated number. I don't
know how I got so lucky, but all the ID numbers created at home were
in the 1600-1700 range, and all the artist ID numbers created at work
were in the 1800-2000 range--so no overlap.

I took all the data out of HomeDatabase's "Artists" table and pasted
it in at the end of WorkDatabase's "Artists" table. Now I've got a
WorkDatabase "Artists" table with ID numbers ranging from 1800-2100.


On to the "Songs" table. I plan to copy all the rows out of
HomeDatabase's "Songs" table and paste them in at the end of
WorkDatabase's "Songs" table. However, each row/record in the "Songs"
table has a number in it under a column/field called "IDArtist." In
HomeDatabase's "Songs" table, of course, those numbers are scattered
around the 1600-1700 range (as were the IDs in the "Artists" table).
These numbers don't seem to be linked with any relationships,
fortunately, so I can change them to numbers in the 2000-2100 range
without problems (which is necessary prior to copying & pasting the
data into the end of WorkDatabase's "Songs" table).



I started out toggling back and forth between windows, saying, "Okay,
'Benny Goodman' has a value of 1612 under ID in HomeDatabase's
"Artists" table and a value of 2021 in WorkDatabase's "Artists"
table. I'll do a "find and replace all instances of '1612' with
'2021' in HomeDatabase's 'IDArtist' field in its 'Songs' table."

Lather, rinse, repeat:
"'Louis Armstrong' is 1613 in one and 2022 in the other. Go to the
'Songs' table and do a Find & Replace All."
"'Glenn Miller' is 1614 in one and 2023 in the other. Go to the
'Songs' table and do a Find & Replace All."
"'Chick Webb' is 1618 in one and 2024 in the other. Go to the 'Songs'
table and do a Find & Replace All."
"'Jimmie Lunceford' is 1619 in one and 2025 in the other. Go to the
'Songs' table and do a Find & Replace All."





There has GOT to be a better way to do it. I mean, I can create a new
table with all the old 1600-1700 values going down one column and all
the new 2000-2100 values going down the other, with matching values
adjacent to each other.

There's got to be a way to use that table as the computer's guide to
replacing values found in the IDArtist field of the "Songs" table, but
I can't remember for the life of me what it is!

*sigh*
-KJ
 
After very carefully making a backup (no, two backups, no, maybe three), use
an update query.

Open the query in design view, add the table-to-be-updated and the
cross-reference table you have. Join on the 'old' values. Now put the new
value (the field name) in the Update To "cell" for that old value field.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
"Join on." Is that something that's easy to find when designing an
update query? I'm sorry...I don't have Access handy right now.
-Katie
 
Katie

Open the (new) query in design view. Add both tables.

Drag the 'matching' field from one table's list and drop it on the
corresponding field in the other table. You've just told Access to "join"
the two tables on their common field.

Take a look at Access HELP on using queries, particularly update queries.
There may be some extra ideas there.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top