Uniting three tables in one, and keeping the link

[Guy Verville]

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

 

[Nikos Yannacopoulos]

Guy,

Here's an idea:

Assumption: currently all ID's start from 1.
Start by making a union query like:

SELECT Composer AS NAME, ComposerID AS CID, 0 AS AID, 0 AS LID
FROM Composers

UNION SELECT Arrangement, 0 AS CID, ArrangementID AS AID, 0 AS LID
FROM Composers

UNION SELECT Lyrics, 0 AS CID, 0 AS AID, LyricsID AS CID
FROM Composers

This will return a field with names and three fields with corresponding
ID's from the three tables. Each person will have as many records as the
original tables he/she has a record in, and in each record there will be
an actual ID in the corresponding field, and zeros in the other two.

The next step is a new, totals query on the previous one, with Group By
on names and Max on the other three fields. This should return just one
record for each person, with the corresponding ID's in the pertinent fields.

Now make a new table Artists with fields:
ArtistID (autonumber)
Name
CID
AID
LID

and populate it through an append query from the previous one (or by
changing the previosu one to an append query directly).

New query with master table and Artists table, linking ComposerID from
master with CID from Artists; make it an Update query , update
ComposerIS to value of ArtistID from Artists;
Repeat the step for the other two ID fields.
If all's gone well, you now have what you want and the thee "old" ID
fields in the Artists table are no longer needed, so you can delete them.

Word of caution: back up your database before you try anything!

HTH,
Nikos

 

[Guy Verville]

Thank you Nikos, I had another type of solution in the Tabledesign forum.
I'll check what's the best for me! Thanks again!

Guy

 

[Nikos Yannacopoulos]

Guy,

I looked at John's proposed solution in the other NG, and (taking John's
reputation into account, I'm glad that) it's essentially the same process.

Nikos