Question about subforms

[mysterj1]

Access 2003, neophyte Access app creator. :banghead:

After entering data into my db tables straightaway for sometime, I've finally gotten some time and want to create a better means to enter my data. My music DB contains info about the artist, album (year released and genre), and the songs on the album (rated 1 to 5). My intent for the form is to allow me to enter the album and song titles initially, then go back to "rate" them at a different time. So, my question:

Would the artist table be the main record source, with the album table as a subform, and the songs table being a subform to that? I am trying to grasp the concept of this so that I can actually LEARN the correct application before I begin the design.

Thanks in advance,
Joe

 

[jdraw]

I would consider the Album as a junction table between Artist and Song.

tblArtists
ArtistID PK
Artistname
other atrist specific info

tblSongs
SongId PK
SongTitle
Other song info

tblAlbum
AlbumID PK
AlbumTitle
AlbumYear
ArtistID FL to tblArtist
SongId FK to tblSongs
other album specific info

 

[mysterj1]

I would consider the Album as a junction table between Artist and Song.

tblArtists
ArtistID PK
Artistname
other atrist specific info

tblSongs
SongId PK
SongTitle
Other song info

tblAlbum
AlbumID PK
AlbumTitle
AlbumYear
ArtistID FL to tblArtist
SongId FK to tblSongs
other album specific info

Understood on the junction table. Are junction tables used primarily for many-to-many relationships? In my case, a song will typically only be on one album (although bands like the Rolling Stones are wonderful marketers and have tons of compilation albums, so a song CAN be on lots of albums).

My thought was to have the artist and album be on the main form, with the song details on the subform. Would I then be able to create a query (to link the artist and album tables) and then have the songs be a subform?

I apologize if these are basic questions; I think it's necessary to understand the subject before I go crazy building...

 

[jdraw]

Junction tables are a means to handle Many to many.
There is a video here that discusses Junction Table
http://www.youtube.com/watch?v=7XstSSyG8fw

Get your tables designed first before getting too involved with forms.

Form/subform are used to display data in 1 to Many relationship.

So An Artist with an Album on the main form and Many Songs on the subform sounds OK to me.
Richard Rost has a video on youtube
https://www.youtube.com/watch?v=YZKN_-P6wck

Good luck with your project.

 

[mysterj1]

Plugging away on this still (amid too many interruptions). I do understand the point made about junction tables; however in most cases a song will only be on one album, so this is more suited for a one-to-many relationship (one album has many songs).

I have looked through all the threads (on forms) and am frustrated that I am not able to correlate the subform to the master.

 

[jdraw]

Remove the lookup at the table level.
see http://access.mvps.org/access/lookupfields.htm