Introduction to Access

What is a Database?

A Database stores information in an easily accessible and space-efficient format. A database consists mainly of several related tables. It is easiest to think of these tables as having a structure similar to that of a spreadsheet. Access Databases make it very easy to pull related information from different tables into an elegant, printable display, create easy-to-use forms and web pages allowing data manipulation (adding, editing, or deleting information), and sort and display information or calculations drawn from one or more tables. Databases are very useful, but the database will not be efficient and problems can arise if the basic principles of normalization are not followed

Why would I use Access?

Spreadsheets tend to get large and unwieldy. This will be clearer with a concrete example. Let's suppose we had a spreadsheet of all the CD's we owned. The spreadsheet would have columns for the album title, artist, recording company, year released, and one column for each song on the CD. This would become a very wide spreadsheet and you would have to scroll over to see all the songs in an album. It would also be difficult to search for a particular song because the songs would not all be in the same column. We could put them all in one column, but then we would have to enter the artist, album title, recording company and year released many times. Databases do not get unwieldy like that. Databases have many related smaller spreadsheets called tables. A database does not need to have the same information entered more than once. An Access database has a find button that allows you to retrieve information quickly and easily. For example, suppose you knew the title of a song and wanted to know the year it was released and the album it was on. Even though you only knew the song title, you could quickly find the album and date released, without putting the song titles in alphabetical order and looking through them until you found the song you were looking for, as most people would do in a spreadsheet.

Access provides a more intuitive way to enter, sort, and delete data. In a spreadsheet, it can be difficult to see which column or row a piece of information is in as you get farther from the row and column labels because there are only lines dividing the pieces of information. Using a form in Access can allow you to enter data all on one screen.

Calculations can be performed in Access as well as in Excel, but unlike Excel spreadsheets, they can display these calculations and any information in the database in an eye-catching, aesthetically pleasing format. This is called a report.

What is Data Normalization?

Normal Form is a way of designing your database in an efficient, useable way. Here are the principles of Normalization:

1. Use meaningful names for your field and table names
   1. Name your fields with a short description of the information (data) it will contain
      1. For example, CD_Title is better than CD or Album
2. Break the data down into the smallest meaningful parts
   1. For example, instead of storing a recording company's entire address in a single field, store it in 4 fields, Street_Address, City, State, and ZipCode. That way, if someone ever wanted to see the recording companies in one state or zip code it would be easy to sort the information.
3. No field should be blank for a sizable number of records.
   1. If this is a problem you are experiencing, break the table into smaller tables to eliminate the above problem.
      1. For example, if you have a table that contains the album title, artist, and a column for each song on that album, it would be better to make 2 tables, one containing the artist and album title, the other containing songs and album names or identification numbers used to refer to albums (since several albums could have the same name, assigning album identification numbers is a good idea)
4. Choose an appropriate primary key for each table
   1. a primary key is used to identify a single row. Each row must have a different value for the primary key.
   2. The primary key should be a field where the data stored there is:
   3. 1. Unlikely to change. Using an artist's name as a primary key is a bad idea because some artists change their names.
      2. Unique. No two records should have the same value for the primary key.
         1. For example, the album ID number would be a good Primary Key, but an album's name or the recording studio would not be a good Primary Key
         2. Unique numbers can be automatically assigned in Access to provide a Primary Key if no unique identifier naturally exists
   4. The primary key should not be blank for any record!
5. Do not store the same data in two places unless it is a foreign key in one of the tables.
   1. For example if the album ID numbers and album names are stored in a table where the album ID field is the primary key, it would be unwise to create a table with the songs from all the albums as well as the album names and album ID numbers. Since the album ID numbers can be used to retrieve the names of the albums, the album names do not need to be included in this table.

Where can I learn more Access?

To learn more about Access you can go to the Student Technology Education Lab (x3003) to sign up for classes on Access or to check out books on this topic. Staff members may contact the Staff Lab (x3043) for more information on getting help with a specific Access project.