Newbie question: A music table

[Guy Verville]

Hello,

I have a Score Sheets Music table that contains among the fields those one:

Composers
Lyrics
Arrangements

These three fields are alike, since they contain names of persons, and since
a person can compose, write and arrange, it would be suitable to have one
table for the names that is linked to those three fields.

The Table Analyser could have be of some help but it creates three different
tables linked to the previous one.

So, my task is to merge the Composers/Lyrics/Arrangements names into one
table, then link those names to the original table which will still have
three fields for Composers, Lyrics, and Arrangements.

I will have a thousand records to proceed (which means 3000 fields to
update)...

Thanks in advance.

Guy

 

[Allen Browne]

The very simplest arrangement would be something like the following.
One artist writes many songs.
One song can have many arrangments.
That leads to these tables:

Artist table
ArtistID AutoNumber primary key
Surname Text
FirstName Text
BirthDate Date/Time
DeathDate Date/Time

Song table
SongID AutoNumber primary key
SongTitle Text name of the song.
ComposerID Number (Long) foreign key to Artist.ArtistID
LyricWriterID Number (Long) foreign key to Artist.ArtistID

SongArrangement table
SongID Number (Long) foreign key to Song.SongID
ArrangerID Number (Long) foreign key to Artist.ArtistID
ArrangeYear Number (Integer) year the arrangement was done.

In practice, you may need something more complex than that, because some
songs also have more than one writer. If that is an issue, you will need to
create a junction table between Artist and Song to handle the many-to-many
relationship. Post back it that is an issue.

 

[Guy Verville]

Thanks Allen for taking time to answer. The problem isn't on how to
configure those tables, but to have juste one table for Artists, while,
presently, I have three existing columns. Those data come from an Excel file
where the entries have been entered manually.

In your example, it means that ComposerID, ArrangerID, and LyricID should
refer to the same Table...

For example, Bach could have arranged something for one piece, composed for
another one. But I don't want to enter Bach in two separate tables. Again,
the problem is that the data exist already, and I have to clean it, merge
it.

Guy

 

[Guy Verville]

Re-reading your post, I understand that you understood the table layout (one
table for all artists). My question remains hower of "Is there a way to
merge my existing data to one table and remove the duplicates while
maintaining the link to the Songs table?"

 

[John Vinson]

Re-reading your post, I understand that you understood the table layout (one
table for all artists). My question remains hower of "Is there a way to
merge my existing data to one table and remove the duplicates while
maintaining the link to the Songs table?"

Yes. A "Normalizing Union Query" can do this.

To populate the Artist table, for example, you could use a query like

SELECT Composer FROM spreadsheet
UNION
SELECT Arranger FROM spreadsheet
UNION
SELECT Performer FROM spreadsheet;

The UNION query will automatically remove all duplicates (note: this
can be TOO effective; Englebert Humperdinck was a 19th century German
composer and also a 20th century performer).

Once you have your Artists table set up, you can do a similar trick
joining it to the spreadsheet, three times, and again do a UNION query
to populate the resolver table.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Guy Verville]

Thank you very much!!!

Guy

Yes. A "Normalizing Union Query" can do this.

To populate the Artist table, for example, you could use a query like

SELECT Composer FROM spreadsheet
UNION
SELECT Arranger FROM spreadsheet
UNION
SELECT Performer FROM spreadsheet;

The UNION query will automatically remove all duplicates (note: this
can be TOO effective; Englebert Humperdinck was a 19th century German
composer and also a 20th century performer).

Once you have your Artists table set up, you can do a similar trick
joining it to the spreadsheet, three times, and again do a UNION query
to populate the resolver table.

John W. Vinson[MVP]
(no longer chatting for now)