Introduction to Microsoft Access 2003

1
Introduction to Microsoft Access 2003

• Mr. A. Craig Dixon
• CIS 100 Introduction to Computers
• Spring 2006

2
Why Access 2003?

• Microsoft Access is used to structure and store
  information that will be updated often and / or
  will be searched by one or more specific
  criteria.
• Common personal uses include storing information
  about collections (movies, sports cards, etc.)
• Common corporate uses include storing sales
  information, employee data, etc.

3
What is a Database?

• Microsoft Access is used for creating a special
  data structure called a database.
• A database is a collection of data stored in
  tables.
• Tables consist of records, which are themselves
  collections of data about a single item.
• Each datum in a record is called a field.

4
You Define the Structure

• Because of the wide variety of data people store
  in databases, a database initially has no tables,
  and thus no fields or records.
• The user must create one or more tables and must
  further define the structure of each table by
  defining the number and types of fields in the
  table.

5
An Example of a Database

• Suppose you want to build a database of movies.
• You might want to define a table with fields for
  the movies title, rating, and year of release.
  You might also define fields for the actors in
  the movie or the production company that produced
  it.
• After defining the structure of this table, you
  enter information about each movie as a separate
  record.
• If you want to build a larger database, you could
  include another table of information about
  different actors
• Fields in this table might include the actors
  age, hometown, etc.

6
Database Keys

• Each table must contain a primary key each
  record in the table must have a unique value in
  the primary key field.
• In databases involving people, the most common
  key is the persons social security number.
• It is sometimes desirable to use more than one
  field as the key this is called using a
  composite key.
• In the movie example, you might want to define
  year of release and title as a composite key,
  since movies are sometimes re-released in
  different years.

7
Field Data Types

• Each field must be given a data type based on
  what kind of data the field will contain. There
  are several data types, including
• Text any kind of alphanumeric data like Excel
  labels, no math functions can be performed on
  text data.
• Number integer or floating-point numerical data
  on which math operations need to be performed.
  Even data like ZIP codes should be of text data
  type, since no math operations are performed on
  them.
• Yes/No the only valid entries are Yes and No
• AutoNumber each record is assigned the next
  consecutive number, beginning at 0. This is most
  useful for primary keys
• OLE object many kinds of external content,
  including sounds, pictures, and documents, can
  also be stored in an Access database using this
  data type.
• Clearly, a great deal of planning is involved
  before constructing a database.

8
Creating a Database in Access

• Upon starting Access, the user must first create
  a database.
• The user is immediately prompted to save the
  database. This is because, unlike other programs
  in the Microsoft Office suite, Access saves
  changes to the database contents as soon as they
  are made.
• Only changes to the design of the database itself
  must be explicitly saved.
• After saving the new database, the user is
  prompted to create a new table using one of three
  methods. The method we will use is Design view.

9
Anatomy of Design View
10
Relationships

• The user can link two tables using an arrangement
  called a relationship.
• In a relationship, the value of a field in one
  table exactly matches the primary key of a record
  in another field. This value is called a foreign
  key.
• In the movie example, instead of entering the
  actors name in the field for the star of the
  movie, you would instead enter the primary key
  value of the specific actor from the actors table.

11
Garbage In, Garbage Out

• Databases are built around the principle of
  garbage in, garbage out (GIGO).
• This means that databases are extremely dependant
  on consistency in formatting of the data in them.
• Even subtle variations like upper-case and
  lower-case letters can make huge differences in
  how a database will respond.
• For this reason, there are numerous controls to
  ensure that data is input in a consistent manner.

12
Structured Query Language (SQL)

• Databases are often quite large and complex,
  which makes finding specific data challenging.
• To make the task less daunting, databases provide
  a feature called querying.
• The user creates a request, called a query, that
  describes what data he or she wants displayed and
  how to display it.
• When the query is run, it creates a presentation
  of data called a view.
• All queries are written in a language called
  Structured Query Language (SQL, pronounced SEE
  kwul). Some people refer to SQL as Standard Query
  Language. The two are interchangeable.
• Anything that can be done to a database (creating
  or deleting a table, adding or deleting a record,
  changing a value, etc.) can be done using a query.

13
A SQL Example

• Say you have a database of movies. You want to
  find all the movies in the database that are in
  the comedy genre and were released between 1988
  and 1998. The SQL query might look like this (SQL
  keywords are in all caps)
• SELECT movieName FROM movies WHERE genreComedy
  AND releaseYear gt 1987 AND releaseYear lt 1999
• This is a relatively simple query most will be
  significantly more complex than this!

14
Access Makes it Easy

• Learning basic SQL takes weeks learning SQL well
  takes years. We dont have weeks or years to
  learn it.
• Fortunately, Microsoft Access provides another
  way.
• We can create queries in a drag-and-drop
  interface using the Select Query feature of
  Access.
• Queries created using the Select Query feature
  are still in SQL Access just hides the messy
  details.

15
Anatomy of Select Query
16
A Simple View

• To create a simple view, just drag the fields you
  want in the view into separate field boxes in the
  Select Query window.
• Once you run the query, your view is created.

Run the query
17
A Sorted View

• There are two methods of sorting
• Ascending least to greatest (numeric) or
  alphabetically (textual)
• Descending greatest to least (numeric) or
  reverse alphabetically (textual)

Run the query
18
Multiple Ordering

• You can sort on more than one field by putting
  the field that defines the primary ordering in
  the left-most column, the secondary ordering in
  the next slot, etc.

Run the query
19
A View With a Criterion

• Sometimes you want to see only data that meets a
  certain criterion. To do this, just enter the
  criterion in the criteria field.
• This query returns records where Wage is less
  than 15.

Run the query
20
A View Using Boolean NOT

• Instead of a positive criterion, you can use a
  negative criterion by using the Boolean operator
  NOT.
• This query returns all records where
  PositionTitle is not Cook.

Run the query
21
A View Using Boolean OR

• You can create a query that returns records that
  meet one criterion or the other by adding all the
  criteria in the or blank(s).

Run the query
22
A View Using Boolean AND

• You can create a query to return data that meet
  all of several criteria by entering several
  criteria on the same line of the criteria field.
  (You can use the same field more than once.)

Run the query
23
Forms and Reports

• Datasheet view often is not the most attractive
  way to view or edit your data. Access provides
  two features commonly used to make these tasks
  more user-friendly.
• Forms allow the user to create a GUI front-end
  for the database where data can be entered,
  modified, and deleted.
• Reports allow the user to create stylized
  printouts of data from the database. Reports can
  be based on a table or a queried view.
• Most high-level programmers will use other, more
  specialized tools for these tasks, but for
  beginning students, they are a powerful extension
  to Access.
• We will create simple forms and reports in our
  in-class project.