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)...
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.
- 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.
"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.
- 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?
- 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.
