Help designing sheet music database

[kingy75]

Hi all,

I need help designing a database for an IT course I'm studying at present (using Access 2007). I'm creating a database of all my sheet music & I'm having some trouble with relationships & lookup lists.
I would really appreciate some advice with my design, please see the attached image for a layout of my tables.

My questions:
1)In the TblSongAlbum – am I required to enter the SongID/Name & AlbumID/Name manually or will this data come from the relationship/s that I create?


2)The same question applies to the Table of Artists – do I have to enter the ComposerName manually? Or will this data come from the relationship that I create with the Composer field in the Table of Songs?
3) Another question. In my tblAlbums Table, I have linked the AlbumTitle field to the AlbumTitle field from the tblSongs Table & created a lookup drop-down list for the AlbumTitle field in the tblAlbums Table. I’m not sure that this was the correct thing to do as now within my tblAlbums Table I receive multiple listings for each album, due to the album title appearing for multiple songs in the tblSongs Table. How do I get each album title to appear once only in the lookup list within the tblAlbums Table?


Any advice would be most appreciated, my project's due this coming Tuesday.


Thanks everyone,
Chris

[NassauBob]

Okay, first question: yes, you will have to enter those related field values manually, unless you do some SELECT INTO queries to do it for you. Relationships really have nothing to do with data, only with the table definitions.

Last question: that result you are seeing is due to the many-to-one relationship. If you were to place the lookup column in the tblSongs table instead, those non-distinct values would not be there, because the source of them will not have repeated values.

P.S. Nice job on your naming conventions. That's the way ya do it! :-)