Any one use Access Database?
Hopefully there are some that use Access as I am trying to set up a database and have a question. The database is CASSETTE TAPE COLLECTION. I have 20 columns starting with ID, TITLE OF CASSETTE, S...
Forum Discussion
I used to create full Access applications for business.
The key concepts here are to create tables listing "entities", and then defining their "relationships". The challenge is deciding what an "entity" is. As a simple example, you might decide that "Cassettes" is an entity - that will be a specific table. It would then have "characteristics" that you want to keep track of: Name, date acquired, condition, etc. Every cassette entry describes one specific object - it may be you have two of the same, and maybe the only difference is when you acquired it, which is fine to enter, as all the info together uniquely identifies one specific cassette.
The second "entity" might be "Songs", which would be the song titles. In this example, you would initially only list each song title once. Do NOT list anything that is already recorded in the "Cassettes" table!
Then you decide what items in each table make the line unique - if you have to combine cassette name, date, and condition to uniquely identify a specific cassette, then you can add another column named "cassette ID" as a primary key to uniquely identify it instead. (You don't have to, as you could use the name-date-condition combination as a primary key all the time, but that gets cumbersome for you to deal with. The "ID" is just a shortcut to make things less complicated.) Whether you assign the ID number, or let the computer do it, is up to you. Access defaults to doing it itself, but you can opt out (or change/delete it afterwards) when creating the table. But, you do have to have a primary key of some kind.
Ditto on "Songs" - either just use the title as the primary key, or create a "Song ID" number as a primary key.
Then you make a third cross reference table, that only lists the two primary keys: "Cassette ID" and the "Song ID". That is the place where you link a song to a specific cassette. That way if the same song is on multiple cassettes, you don't have to re-enter all the information in the "Song" table.
OK - what about Artists? In pure design, they would be their own table. And there would be a cross reference table to "Songs". And by linking everything in a query, you could easily find every cassette that has Paul Simon on it. Or all the Paul Simon songs you have.
But, as you can see, things get complicated fast, if you want to be really pure about the design - you'll have lots of entity tables, and lots of cross reference tables. Access does a great job of allowing you to create forms that have parts of all the tables presented on one screen, so it looks like you are entering everything in one spot - and managing all the ID's for you. But that gets more advanced than you probably are at this point.
Just don't get too OCD about trying to figure out what is an Entity in it's own right, and what is a characteristic of another entity.
So, it is permissible to have a column in the "Songs" table that lists the Artists name, instead of a separate Artists table (we've now decided that the Artist is just a characteristic of a song, not an entity in it's own right). Just means you have a bit more typing to do when entering a song covered by more than one artist - and the song title will be no longer unique by itself, so you probably would want an ID column, if you didn't already have one.
Sounds like it is time for you to go get a used book! "Access for Dummies" is excellent, as are the ones by Microsoft. Don't worry if you can't find one for your exact version - you are looking for one that will cover concepts. So any version reasonably before or after yours will do. Some of the "how to specifically do it" will be different, of course - but not outrageously so.
The key concepts here are to create tables listing "entities", and then defining their "relationships". The challenge is deciding what an "entity" is. As a simple example, you might decide that "Cassettes" is an entity - that will be a specific table. It would then have "characteristics" that you want to keep track of: Name, date acquired, condition, etc. Every cassette entry describes one specific object - it may be you have two of the same, and maybe the only difference is when you acquired it, which is fine to enter, as all the info together uniquely identifies one specific cassette.
The second "entity" might be "Songs", which would be the song titles. In this example, you would initially only list each song title once. Do NOT list anything that is already recorded in the "Cassettes" table!
Then you decide what items in each table make the line unique - if you have to combine cassette name, date, and condition to uniquely identify a specific cassette, then you can add another column named "cassette ID" as a primary key to uniquely identify it instead. (You don't have to, as you could use the name-date-condition combination as a primary key all the time, but that gets cumbersome for you to deal with. The "ID" is just a shortcut to make things less complicated.) Whether you assign the ID number, or let the computer do it, is up to you. Access defaults to doing it itself, but you can opt out (or change/delete it afterwards) when creating the table. But, you do have to have a primary key of some kind.
Ditto on "Songs" - either just use the title as the primary key, or create a "Song ID" number as a primary key.
Then you make a third cross reference table, that only lists the two primary keys: "Cassette ID" and the "Song ID". That is the place where you link a song to a specific cassette. That way if the same song is on multiple cassettes, you don't have to re-enter all the information in the "Song" table.
OK - what about Artists? In pure design, they would be their own table. And there would be a cross reference table to "Songs". And by linking everything in a query, you could easily find every cassette that has Paul Simon on it. Or all the Paul Simon songs you have.
But, as you can see, things get complicated fast, if you want to be really pure about the design - you'll have lots of entity tables, and lots of cross reference tables. Access does a great job of allowing you to create forms that have parts of all the tables presented on one screen, so it looks like you are entering everything in one spot - and managing all the ID's for you. But that gets more advanced than you probably are at this point.
Just don't get too OCD about trying to figure out what is an Entity in it's own right, and what is a characteristic of another entity.
So, it is permissible to have a column in the "Songs" table that lists the Artists name, instead of a separate Artists table (we've now decided that the Artist is just a characteristic of a song, not an entity in it's own right). Just means you have a bit more typing to do when entering a song covered by more than one artist - and the song title will be no longer unique by itself, so you probably would want an ID column, if you didn't already have one.
Sounds like it is time for you to go get a used book! "Access for Dummies" is excellent, as are the ones by Microsoft. Don't worry if you can't find one for your exact version - you are looking for one that will cover concepts. So any version reasonably before or after yours will do. Some of the "how to specifically do it" will be different, of course - but not outrageously so.
