Multiple Track Listings

[Goatflavor]

I am new to Access and would appreciate any input that would assist.
I'm cerating a database for my recordings, (records, cds and live
recordings) and want the ability to search and see if I have a song.
I have one table which autonums an AlbumID for each entry, then I
specify using checkboxes if it is a 45, 33 1/3, 78 or CD (as well as a
ton of other data I want to keep track of.)

My question: I'm thinking that another table with the AlbumID and
then Track. Then I could insert a subform into my current data entry
form to enter each track. Does this sound like the way to go?

If so, I'm thinking I need to insert an event into my subform that
will create a new entry when TAB is depressed, this will allow me to
enter two tracks, or 50 tracks. Thoughts on this? If so, can someone
point me towards a good resource to borrow some code?

I appreciate your help.

 

[tina]

there is a one-to-many relationship between albums and tracks, so yes, a
separate table from tracks, with an album id field as a foreign key linked
to the primary key AlbumID in the albums table, is the correct way to set up
the tables/relationships. once you do that, you can create a main form bound
to the albums table, and add a subform bound to the tracks table. within the
main form's Design view, select the subform control and set its'
LinkMasterFields property to AlbumID and its' LinkChildFields property to
the album id field. now, in the main form's Form view, you can move to (or
add) an album record and enter as many track records in the subform as you
wish, for that album - without any code at all.

and btw, suggest you do *not* use Yes/No fields to assign attributes of an
album, such as type of media. in doing that, you're storing data in
fieldnames, such as "45" "78" "CD", which breaks normalization rules.
instead, have a single field named for the attribute, such as MediaType.
create a table called, for instance, tblMediaTypes with a minimum of two
fields such as MediaID (primary key) and MediaName. each type - 45, tape,
CD - will be one record in the table. that table will have a one-to-many
relationship with the albums table - one media type may apply to many
albums, but each album is only one media type. in the form, use a combobox
control, with RowSource from tblMediaTypes, so you can choose the
appropriate media type for each album record. and btw again, if you have the
same album on more than one media, for instance both tape and CD, then you
have a many-to-many relationship which requires a different
tables/relationships/forms setup.

hth