Forum Discussion
Linking tables using relationships
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 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!
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.
- 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!