Any one use Access Database?

CloudDriver wrote:

2oldman wrote:

CloudDriver wrote:
Each cassette name is entered once, each song name is entered once, even if it is on more than one cassette - each artist name is entered once, even if that artist sings many songs on many cassettes.

How would you search your database design to find out how many cassettes Artist A appears on? Perhaps I'm misunderstanding 'artist name is entered once.'

This is probably way too much for this application, but for searchable internet databases, yeah.

As I said, it's more complicated than that.

For a cassette database, I would create a data entry form that showed one cassette and also a subform that showed the list of songs and artists for that cassette. The subform would have a drop down list containing the song names that had already been entered for other cassettes, along with the option to enter a new song name. The artist field would also have a drop down for previously entered artists, plus the option to enter a new artist. By using drop downs, the song name and artist name needs to be entered only once in a table. A query would likely be needed when creating the subform vs. using just the tables.

Since an artist can sing on more than one cassette and a song can be sung by several artists and on more than one cassette, two more tables are needed to store the links (which are the table primary key field numbers).

And yes, it is easy with a database to do searches by artist name, song name, etc. to display which cassette or cassettes have a particular song or artist. It wouldn't be necessary to type in an artist name, just use a drop down list of the names already entered to select the desired artist.

Databases are extremely powerful, but are best used when there is a lot of data. 14 cassettes isn't a lot of data, so a spreadsheet is easier/quicker.

Thanks for all the replies, there is some good info here!

I have an Excel spreadsheet (over 10,000 lines) but it is getting cumbersome to use (so I may have it set up the wrong way)and thought that maybe Access would be easier to use, but then again maybe not!:(

To clarify, I like cassettes and have about 300 on hand that I pick up at estate sales and transfer to the PC. I wanted to try Access and if it works use it to include my CD's (about 350) and vinyl records (about 1,100).

In Access I have 2 tables, one for the cassette info: name, artist, label,copyright, date acquired, cost, location, condition, etc. The ID in the table is the Primary Key (Autonumber). The other table is the one I have the question about and the ID is also the Primary Key (Autonumber). The relationship between the 2 tables is the ID. I thought about have an additional relationship being the Title Of Cassette field, but not sure if that would be needed. I could set up a third table just for the artist name and it's ID would be the primary key (didn't think about that).

I understand now about its not necessary to have both ID and Title Of Cassette in order, but my mind wants it all to be in order (OCD?) I am not sure how to do a "one to many" relationship. I am still learning and thank all for your input. Now to figure out forms, queries, etc.

obgraham - I did use a hyperlink in the last column of the Excel spreadsheet that on clicking with the mouse would bring up and play Youtube or whatever for as many songs as I could locate a link. I would expect that a hyperlink could be set up with itunes. Hmmm, maybe a hyperlink would work in Access.

Thanks
Wayne