Linking tables using relationships

[Jqws13_]

Hi All, Just got a minor problem with an access database that I am creating. I have two tables, "Song" and "Artist". For some songs there are multiple artists, so in the Artist_ID field (Foreign Key), I selected, "Allow Multiple Entry". I am now trying to link the two tables, but I am not sure how. For each artist there are many songs, but some songs have many artists. 

  

[#2]

@Jqws13_ 

You are in luck. I created this relational database application a few years ago. 

Even the names of the tables are almost the same. You have a few additional attributes (death date of the artist), but this is what you need to manage the data. 

I see you included an attachment field, which is going to be a problem, most likely. Storing binary files internally in tables will cause the accdb to grow to an out of control size quickly. It's best to store images and song files (assuming that's what you have) externally and use links to the files.

[#3]

Also, because these songs are typically on albums, you also need a separate table for albums. Link the artist either to the album or to a song, depending on how you want to enforce the rules.

[#4]

MVF won't do you good, unless you are familiar with it (and can handle it).
just create a Junction table for your ArtistSong.

[#5]

Agree with previous comments about avoiding attachments and multivalued fields.
You might find my 3-part article useful: Relationships and Referential Integrity

It also includes an example on albums and album tracks.

[#6]

Sorry, but the link you provided does not seem to work. With this being a small scale project, the attachment fields (Pictures) are not going to be a issue, and I am not attaching the song files. My Full Database has 5 Tables; Album, Artist, Band, Record Company, and Song. All of the relationships are fine, apart from the Artist Song relationship, as I have some songs, like the Rolling Stones songs, that have two artists working on them.

[#7]

@Jqws13_ 

"Sorry, but the link you provided does not seem to work" 

Please be more specific. What DID happen? If I know the problem, I can fix it. 

I would not be too confident in the use of internal attachments of binary files. That's long been a trap for newer Access users. 

I have an alternative location for downloads, perhaps this one will work better for you. Search this Public OneDrive for MusicandArtist.

 It illustrates the many-to-many relationship you need and the preferred table design to support it.

[#8]

I gave creating a junction table a go, as well as a MVF table. When I gave running a query a go, for both methods, I would get two records for the songs with multiple artists. When I create a form, with subforms (As that is what we have been asked to do), The MVF only allows me to create one subform, lumping the song and artist data together, whereas the junction table allows me to create two subforms. Is there any way for the two records to be merged into one, with the artists appearing such as "Mick Jagger, Keith Richards". Thanks for all your help!

[#9]

The link that you did provide only reloaded this forum page. There were two zip folders I found under MusicandArtist, but when I opened them, I could only view the switchboard. Is there something else I need to do?

[#10]

@Jqws13_ 

Aside: I dislike the way this forum provides notices of responses and the way it displays them. It's too easy to miss something. 

No serious Access developer with much experience uses MVFs in tables. They are useful in a limited number of circumstances, primarily as pick lists, i.e. static choices that users pick from, but not variable data like artist. Here they would be clunky at best.

For DISPLAY PURPOSES only you can concatenate multiple values, such as the names of artists, using VBA. There are several demos available as downloads around the internet. Here's one of the better ones, IMO.

[#11]

@Jqws13_ 

I got the link wrong. Sorry.
The demos are full working sample databases. You have to look at them in design view to see how they are put together. Look at the relationship diagram and the forms and subforms and the code behind them. 

[#12]

Due to my limited knowledge of access, and VBA, I think I think I will just stick with having multiple entries for the same song. I'll let you know if I run into anymore issues. Thanks!