Msny-to-many - how to?

[Kurt Hansen]

I have a database of classical music with 32,000 records where the field "Composer(s)" is entered in the format "composer_1 / composer_2 / composer_3" etc. I want each name to be clickable, so if I click on "composer_2" then opens a window showing the CDs we have with this composer.

When the "Composers (s)" holds up to 15 composers (composer_1 / composer_2 / composer_3 etc.) 15 columns in the primary database will upset and confuse and I suppose that I need to create an independent database of composers?

In the composer's database I want to list all the CDs in the primary database that contains music by that composer.

How do I do it? Please advice me with an easily understandable sketch for a start.

Edited June 8, 2015 by Kurt Hansen

[comment]

 I suppose that I need to create an independent database of composers?

​You need to have a table of Composers, where each composer has one unique record, and another table to join the Composers to your existing CDs table, where each record will be an instance of a composer being featured on a CD. This is the standard method to resolve a many-to-many relationship in a relational database - see:
http://fmforums.com/forums/topic/50942-portal-grouping-problem/?do=findComment&comment=239210

 

You may also consider splitting the CDs to individual compositions - see this very recent thread:
http://fmforums.com/forums/topic/97225-music-recording-library-database-help-please/

 

 

[Kurt Hansen]

Thanks, Consultant, I will take a look at these.