I want to create an inventory of my music CD's. Most of my CD's are collections of songs from various artists. I want to be able to have a list of what's on each track. If I am looking to play a specific song, I would like to be able to request that song and have Access show me which CD it is on.

Someone from another Q&A service gave me this answer: You are going to need 3 tables.
One: The Artist(s), etc.:
Two: The CD Title, CD ID, author ID, etc.,
Three: The Playlist CD ID, etc.

I am afraid I need more specific information that that. I asked a follow-up question, but the person never answered it.

What fields would you suggest in each table?

What is the CD ID and the author ID?

I guess they didn't answer because it's kind of a theoretical question. There are numerous ways to design the table structure for such a system. The ID's are probably refering to the unique or "primary" key in the table. See, each table in a database has to have one, or a combination of fields that are unique to the row that they are in.

For starters just create the tables
"Artist," "CD," and "Song" and make sure each one has a primary field, and make a field for each type of information you think is important.

The key to making the database work like you want is in relating the tables to each other.

This can be done with a "foreign" key which is a field in one table that represents the primary key in another table.

You could also probably accomplish what you want by the way you construct your sql statements, joining one table on the other in a query.

Of course, in true Microsoft style - Access seems to disuade the idea of learning sql, which is really easy, instead they present you with this bizzarre bunch of "wizards" and "menus" and "buttons" and "boxes" and Jeeze there's only about 10 freakin Sql statements that you usually use anyway. OK I'm off the subject....

Access has this feature where you join tables on each other by drawing graphical links. You'll probably want to link the tables something like this:

"Artist"<--->"Song"<--->"CD"

Then in your "Song" table include an Artist_ID field so you can relate songs to artists, and create a "CD" id field so you can relate CD's to songs.

Then construct your querys to search the tables for whatever information you want!

Database design is not easy - if you're not familiar with access spend some time reading and playing around - if you've never worked with databases it's a fairly steep learning curve - but quite doable.

Becky,

Access is an amazingly versatile program. One thing to understand - especially if you've worked with other databases - is that Access is a "relational" database. Some of the others are "flat" databases.

Databases are similar to a box full of index cards. In a "flat" database, ALL the information about each item is "written" on EVERY card and filed in one box. In a "relational" database, different parts of the information may be written on several different cards, which are kept in different "file" boxes (tables), which are "linked" with codes. (Keep reading, it'll make sense.)

In a flat database, you would set up a table with fields like:
Artist, Song Title, Album Title, CD Track
and enter the COMPLETE information for each song. That works, but it can be time-consuming and you can have errors and inconsistencies.

In a relational database, you could set up one table for Artists, another for Album Titles, and another for Song Titles.
In the Artist table you would enter the artist's name and, probably*, a "code" to identify the artist.
In the Album Titles table, you would enter the album name and, probably*, a "code" to identify the artist.
In the Song Titles table, you would enter the song title, AND the artist CODE, the album CODE, and the CD track number.
Then you would create a link between the Song Titles table and the other two tables LINKING the CODES. Basically, what happens is you tell it to go "find" the artist and album name based on the codes you give it.
Some advantages of using the relational table setup are:
If you need to change the name of an artist (say you misspelled it), you change it in the Artist table only, and it's automatically the same in all locations.
The name of artists and albums is always consistent. You won't have a problem with entering things differently - like 1st and First.

* There is also a somewhat simpler approach to using multiple tables to lookup information as you input it, but I'm not sure that it would be especially helpful for your situation.

One great thing about Access is that you can use it as a "flat" database OR as a "relational" database.

Since you would most likely be entering your data off of each album, you would probably do very well using the "flat" approach - entering all the data into each record. (That would eliminate messing with "codes.") Access has a feature where you can duplicate the data from the previous record so it would go very fast and would also be consistent. You could use something like Artist Name, Album Name, Track Number, and Song Title. You would just hit the "duplicate field" code (ctrl ") for Artist Name and Album Name.

Another great thing about Access is that you can easily add another field if you want. Say, you decided later that you wanted to be able to sort by "Type of Music" (Western, Rock, Gospel). You could add that field to your table and then fill it in on your records.

One question in regards to how you set up the tables is, "Do you just want to get the information entered and use it, OR do you want to learn some of the complexities of designing databases?" (There is no "right" OR "wrong" answer. It just depends on what you want.)

I am currently using Access 97, but have used Access 2000 a little. (They are very similar.) You are welcome to email me, and I'll be happy to help you set up your tables, fields, and queries.

Teresa