Apr 13 2022 07:21 PM
Apr 13 2022 07:21 PM
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.
Apr 13 2022 07:40 PM
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.
Apr 13 2022 07:42 PM
Apr 14 2022 04:09 AM
Apr 15 2022 03:59 AM
Apr 15 2022 06:35 AM
"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.
Apr 15 2022 06:45 AM
Apr 15 2022 06:51 AM
Apr 15 2022 06:58 AM
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.
Apr 15 2022 07:04 AM
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.
Apr 16 2022 08:19 PM