Combining tables, removing duplicate, keeping the link with a fourth table

Hi,

I have received a Music score database which contains, among others, a
table that has three separate fields: ComposersID, ArrangementID,
LyricsID.

These three fields point to three separate tables: Composers,
Arrangement, Lyrics.

However, these three tables contain also duplicates between them (e.g.
Bach can have arranged, or wrote also the lyrics). So, my task is to
unite the three tables of artists names into just one. BUT, the links in
the first table (MusicScore) should remain intact!

For example, one record has this: ComposerID 1 = Bach, ArrangementID 34
= Bach, LyricsID 22 = Bach (remember, three separate tables here)

In the updated MusicScore table, all these fields should point to a new
Artists table (hence, an imaginary ID 25 for Bach).

I'm a little lost...

Guy

On Tue, 14 Dec 2004 14:16:21 -0800, Guy Verville
wrote:

In the updated MusicScore table, all these fields should point to a new
Artists table (hence, an imaginary ID 25 for Bach).

I'm a little lost...

That's a very good direction in which to move, and it's not all that
hard to set up the relationships.

The trick is to add MusicScore to the Relationships window, and then
add Artists *three times*. Access will name them Artists, Artists_1,
and Artists_2; you can see three icons in the window but it's just one
table displayed three times. Join each instance to one of the
MusicScore fields.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

John, thank you for answering, but I don't have yet this Artists table! I
have to combine the three tables before, Composers, Arrangers, and Lyrics
into the Artists (which is not yet built). This is my problem.

Look at this image that explain it more clearly.
http://www.guyverville.com/divers/relations.jpg

I need the tables Musiciens, Arrangement, and Paroles are similar tables,
but there independant entities. How to combine them, to remove the
duplicates, without loosing the relationships to the 7000 records of the
main table?

On Tue, 14 Dec 2004 19:46:45 -0800, Guy Verville
wrote:

John, thank you for answering, but I don't have yet this Artists table! I
have to combine the three tables before, Composers, Arrangers, and Lyrics
into the Artists (which is not yet built). This is my problem.

Look at this image that explain it more clearly.
http://www.guyverville.com/divers/relations.jpg

I need the tables Musiciens, Arrangement, and Paroles are similar tables,
but there independant entities. How to combine them, to remove the
duplicates, without loosing the relationships to the 7000 records of the
main table?

If the names are stored *exactly* the same in the three tables a UNION
query will do this for you nicely.

You need to go into the SQL view to do this; type into the box

SELECT Musique FROM Musiciens
UNION
SELECT Arrangement FROM Arrangement
UNION
SELECT Paroles FROM Paroles
ORDER BY 1;

This will remove all the duplicates - all the EXACT duplicates. If one
table has "Liszt, Franz" and the other has "Liszt, Franz" you'll get
both - there's an extra blank. These can be edited out manually when
you're done.

Save this query as uniArtists and create a new, empty table Artists
with two fields - ArtistID and Artist. Base an Append query on
uniArtists appending the names to Artists, letting it autonumber.

You'll finally need to run three Update queries to update the old
foreign keys to the new values. For example,

UPDATE ([Catalogue] INNER JOIN Paroles ON [Catalogue].[Paroles] =
[Paroles].[Numero]) INNER JOIN Artists ON [Paroles].[Paroles] =
[Artists].[Artist]
SET Catalogue.Paroles = Artists.ArtistID;

and similarly for the other two fields. You're joining the old ID
number from the catalog, to the old Paroles table to find the artist's
name; joining by that name to the names in the Artists table; and
picking up the newly assigned ID.

This DOES have flaws. If you have Englebert Humperdinck the composer,
and Englebert Humperdinck the lounge singer, in different albums - or
other cases of different people who happen to have the same name -
you'll need to find and repair these because they'll all get lumped
under one ID. But it should do the bulk of the work for you.

Good luck!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Thanks you very much for this invaluable information!

Guy