New Perspectives on Microsoft

1
Microsoft Access 2002

• Tutorial 1 Introduction To Microsoft Access 2002

2
Define some key Access terminology

• Field A single characteristic or attribute of a
  person, place, object, event, or idea.
• Record A set of related field values.
• Table A collection of records that identify a
  category of data, such as Customers, Orders, or
  Inventory.

3
Illustration of fields, recordsand a table
Fields are grouped into records, and records are
grouped into a table.
4
Relational database and keys

• A relational database is a collection of tables
  that are related to one another based on a common
  field.
• A field, or a collection of fields, is designated
  as the primary key.
• The primary key uniquely identifies a record in
  the table.
• When the primary key of one table is represented
  in a second table to form a relationship, it is
  called a foreign key.

5
Relating tables using a common field
The primary key in the Employer table
(EmployerID) is the common field that relates
this table to the Position table.
PositionID is the primary key in the Position
table. The EmployerID field is a foreign key in
this table.
Primary keys can only have one occurrence in a
table. Foreign keys may have multiple occurrences.
6
Open an existing database

• To open an existing database, you must first
  start Access.
• When Access is launched you will see the Access
  window, with the task pane on the right side of
  the window.
• From the task pane you can open an existing
  database by
• Selecting the database you want from the list of
  Recently opened databases
• Selecting More files to choose a database you
  want to open from a storage location
• If you choose More files you will browse to the
  correct location of your database, click on it,
  and then click on Open to open the database

7
Use the task pane to open a database
You could open a database by clicking its name,
or by clicking Files to open a dialog box to find
the database you want.
The task pane allow you to open existing
databases or to create new ones.
8
The Open dialog box
You can open an existing database by locating the
drive and folder containing the database in the
Look in list box.
Select a database by clicking on its file name,
and the Open button will then activate. Click the
Open button to open the file.
9
The Access window

• When a database is opened, the Access window and
  the Database window will be displayed.
• The Access window contains a menu bar, a toolbar,
  a task pane, and a status bar.
• In the Access window, use the task pane to create
  a new database or to open an existing database.
• To create a new database, make a selection from
  the New section of the task pane
• To open an existing database, select from the
  list of Recently opened databases or from the
  More files option

10
The Database window

• The Database window is the main control center
  for working with an Access database.
• The Database window contains a menu bar, an
  objects bar, and a groups bar.
• The Objects bar lists all the objects available
  in the database
• The list of objects consists of tables, queries,
  forms, reports, pages, macros, and modules
• You can click on any of the objects in the
  Objects bar to obtain a list of objects of that
  type

11
The components of the Accessand Database windows
12
Use the Objects bar to view database objects
To see a list of objects in a category, click
that category on the Objects bar.
The Queries category has been selected, and a
query named Contacts is shown in the window.
You can also create new objects within each
object category.
13
Open an Access database table

• To open a table you must first open a database
• In the database window, select Tables on the
  objects bar
• In the list of tables, select the table you want
  to open and click on the Open button
• When a table is open it is in Datasheet view,
  which shows the table's data as a collection of
  rows and columns
• Each row in the Datasheet view represents a
  record in the table

14
Navigate a database table

• You can navigate through the records by using the
  navigation buttons displayed at the bottom of the
  Datasheet view window.
• The navigation bar allows you to
• Go to the first record in the table
• Go to the previous record in the table
• Go to the next record in the table
• Go to the last record in the table
• Create a new record for the table (sometimes
  called an append record)

15
A table in datasheet view
16
The navigation bar buttons
17
Learn how Access saves a database

• The Save button in Access differs from the Save
  button in other Windows programs.
• When you press the Save button in Access, you are
  saving the design of the Access objects and NOT
  the data itself.
• Access saves data as it is entered
• For this reason, the location at which you are
  storing your database must always be accessible
  while working with a particular database.
• If the database is located on a diskette, the
  diskette must be in the diskette drive at all
  times while working with this particular database

18
What is an Access query?

• If you want to see just a portion of the data in
  a table you can create a query.
• A query is a question you ask about the data
  stored in a database table.
• Access responds by displaying the data according
  to your question.
• For example, if you ask to see all the customers
  from New York, the response would be to display
  only the records whose state field matches with NY

19
Open an existing query and create new queries

• You can open an existing query by clicking
  Queries on the Objects bar and then selecting the
  query you want to open.
• You can also create your own queries by clicking
  New on the Database window.
• To create a new query, you can use the Simple
  Query Wizard, which will bring you through the
  selections you want for your query.

20
Navigating a query and sorting the results

• When you run and get the results of your query,
  you can reorganize the data by sorting the
  datasheet in either ascending or descending
  order.
• Click the pointer anywhere in the column you wish
  to sort
• Click the Sort Ascending or Sort Descending
  buttons on the Query Datasheet toolbar to sort
  the results in the desired sequence
• You can navigate through the records by using the
  navigation buttons on the Navigations toolbar.

21
Sample Query Datasheet view
Query Datasheet view looks very similar to Table
view. However, the data displayed in a query does
not have to be in the same sequence as it appears
in table view. You can display fields in any
order in a query.
22
Selecting fields to include in a query

• You can create a query that will display only
  selected fields from a table instead of
  displaying all fields.
• In the Simple Query Wizard dialog box, select
  which fields you want included in the query.
• Move all the fields into the Selected Fields box
• Move the fields one at a time
• Remove fields out of the Selected Fields box by
  pressing one of the remove buttons
• If you wanted to select all the fields except
  one, you can move them all to the selected fields
  list and then remove the one field you don't
  want.
• Once you have made your selections, press Next to
  move to the next dialog box in the Wizard.

23
The Simple Query Wizard
The Simple Query Wizard assists you in the
creation of a new query.
24
Create an Access form

• A form allows you to view your data one record at
  a time.
• Forms are useful for maintaining, viewing, and
  printing records in a database.
• You can create your own form in the Forms Design
  window or you can use the Forms Wizard to create
  a form.
• The easiest way to create a form is to use the
  AutoForm Wizard.

25
Use the AutoForm Wizard

• The AutoForm Wizard uses a table (or query) you
  select as the basis to create a form that
  displays all the fields of the table (or query).
• Once you have created your form, you can view the
  records one record at a time.
• The form has a navigation bar just like the
  navigation bar you have already used in the Table
  or Query Datasheet view.

26
Access Form View
Forms display one Table or Query record at a time.
The navigation bar has the same buttons seen in
Table and Query Datasheet view.
27
Create, preview, and navigate a report

• You can create a report, which is a printed
  version of your data, that is formatted according
  to your specifications.
• The data in the report can consist of data from a
  single table or multiple tables.
• Access has a Reports Wizard that allows you to
  easily create a report.
• The report can be based on a table or it can be
  based on a query.
• If the report is based on a query, it will
  contain the same fields that were selected for
  the query

28
The Report Preview window
When a report has been created, you can preview
it on the screen.
You can navigate through the pages in the report
by using the navigation buttons at the bottom of
the preview.
29
Backup and restore a database

• You may want to create a backup of your data so
  that if you lose or damage your database, you can
  recover from the backup.
• You can use a backup tool, such as the Microsoft
  backup tool, or some other backup program.
• To restore the data from the backup, you need to
  use the same backup tool according to the
  instructions associated with that tool.

30
Compacting a database

• It is a good idea to periodically compact and
  repair a database to recover wasted space created
  by adding, deleting, and modifying records.
• Access has a Compact and Repair feature
• Open a database
• Click on Tools and then Options
• On the Options menu, select Compact on Close.
  Selecting this option will cause your database to
  be compacted and repaired every time you close it

31
Setting the Compact on Close option
Select the General tab of the Options dialog box.
Click the check box to insert a check mark so the
database will be compacted every time it is
closed.
32
Compacting reduces database storage size
Compacting a database reduces the space used by
the database, making the space available for
other uses.
33
Convert an existing databaseto Access 2002 format

• It is possible to convert a database created in
  Access 2000 to an Access 2002 database.
• To convert an Access 2000 database to Access 2002
  format
• Make certain the database to be converted is
  closed
• Click Tools on the menu bar, then point to
  Database Utilities
• Point to Convert database and choose the format
  to convert to
• Enter the name of the database to convert, and a
  name for the converted database to be stored under