Vinyl Record collection

[sgillispie]

Hello.
I'm working on a database for my vinyl record collection.
I have a table that has a list of the Artists. This table has ArtistID (primary key) and ArtistName and only has 1 entry for each Artist

I have another table that has a list of the Albums. This table has ArtistID, AlbumId (primary key), AlbumName, Genre (and other album related attributes I'm associating to each album) and can have multiple artist entries for each album by that artists. For example, if I have 4 RUSH albums, it will list RUSH 4 times - once for each vinyl record I have.

I have a third table that lists the Inventory. This table has ArtistName, AlbumName.

I'm trying to create a form that will allow me to select an Artist from a drop down and then have a 2nd box (not sure what type of box to use) display the list of all the albums I have for that artist.

Can anyone help me construct this?

 

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/CascadingCombo.htm

 

[sgillispie]

That is very close and very well could be the solution I'm looking for. However, I'm not fully understanding the coding instructions. Unfortunately, I need more descriptive instructions.

To put it very simply, essentially what I'd like to do is create a way to have a drop down that lists out the 'Artist Name' from the Artist Table and then display all the 'Albums' that I have for that artist from the Album Table (or Inventory Query).

I want the artist name to only be listed 1 time (thus why i'm pulling from the Artist Table). I've tried 'DISTINCT' when trying to pull artist from the Albums Table - but that seems to give me more problems than solutions.

I 'think' Form is the right application, but if it isn't, I'm OK switching to the proper application (e.g. form, query, report, etc.)

 

[jdraw]

Do you intend to keep a list of Songs by artist or Songs on Album?

Here is a data model that may give you some ideas.

http://www.databaseanswers.org/data_models/cd_collection/index.htm
and another
http://www.databaseanswers.org/data_models/record_label/index.htm

 

[sgillispie]

Thank you.

I will eventually have songs listed by album. I have strong table structure and have setup my tables with 'best practice' design. So once I'm able to get my short term goal to work, I'll work the long term goal.

For me right now, the issue is trying to get my report to work correctly where I can select Artist and then see a list of albums from that artist. Eventually I'll have the songs listed out once I do the 3rd layer of cascading combo boxes.

Meaning my short-term goal is to have it where I'm able to select:
Artists (combo box 1) > Album(s) (combo box 2) (all albums listed out)

My long-term goal is to have it where I'm able to select:
Artists (combo box 1) > Album(s) (combo box 2) > Combo box 3 lists songs for Album selected in Combo box 2 (all songs listed out)

 

[jdraw]

I have strong table structure and have setup my tables with 'best practice' design.

Can you post a jpg of you tables and relationships? You may have to zip it because of your post count.