Course Description

This class introduces Microsoft Access. You will be shown how to use tables, views, forms, queries, SQL and reports. Using this knowldge you will be able to create data driven applications based on Microsoft Access.

Keys

The word key is used in several different ways in this course. In the most general sense a key is some symbolic way of referring to data.

Primary Key - The primary key for a row is the key that uniquely identifies this row.

Foreign Key - A foreign key is used in a row to reference the primary key of another row. For example, if the row holds an employee, a column named "supervisor" would be a foreign key to another employee record.

Surrogate Key - When a surrogate key is used, a number is made up to represent the row.

Natural Key - A natural key is more than just a number assigned to a row. It has meaning for the data. For example, if the key MO is used for Missouri, that is a natural key.

Store Data in a Standard Way

When designing the layout of your database it is often valuable to store the data in a standard way. Consider a city name. If you allow the city name to simply be entered into a text field, the city St. Louis may end up being stored in any of the following ways:

St. Louis
St Louis
Saint Louis

Because of this it is better to create a city table, and store a foreign key.

When to use Text or a Number

Know when something is text or number. If you want to do math on
a field, then make it a number. If it does not make sense to do
math on a field, do not make it a number, make it text. Even
if it is numeric, i.e. a phone number, ssn, or zipcode should be text.

63017 + 1 =
555
1212 + 1

10.50 * 8 =

498121212
(314) 636-1212
314-636-1212
3146361212

2007-1-1
1/1/2007

SELECT ',Last FROM Employee WHERE ID = 123