Access project

profiley73t20851
DesignRelationalDBwithAcess.ppt
Home>Information Systems homework help>Access project

Exploring Microsoft Access




By

Robert T. Grauer

Maryann Barber

Exploring Microsoft Office - Microsoft Access Chapter 1

Agenda

  • Introduction
  • Tables and Forms
  • Reports and Queries
  • Relationships and Charts
  • Referential Integrity and Forms
  • Many-to-Many Relationships

Exploring Microsoft Office - Microsoft Access Chapter 1

Introduction to a Database

  • Defining a database
  • Database window in Access
  • The six items in window: Tables, Queries, Forms, Reports, Macros, Modules

Exploring Microsoft Office - Microsoft Access Chapter 1

Database Window

Database
Window

Object Buttons

Menu Bar

Toolbar

Exploring Microsoft Office - Microsoft Access Chapter 1

Datasheet View

Current Record

Total Number
of Records

Triangle indicates

Data has been

saved to disk

Exploring Microsoft Office - Microsoft Access Chapter 1

Design View

Primary Key

Field Names

Field
Properties

Data Type

Description

Exploring Microsoft Office - Microsoft Access Chapter 1

Forms, Queries, and Reports

  • A form is a friendly interface for entering or modifying a table
  • A query provides a subset of a table based on a criteria
  • A report presents data in an attractive format

Exploring Microsoft Office - Microsoft Access Chapter 1

Database Form

Table Name

Go To
Next Record

Go To Last Record

Go To

First Record

Field Names

Exploring Microsoft Office - Microsoft Access Chapter 1

Report

Report Header

Page Header

Close Print Preview

Detail Section

Zoom

Exploring Microsoft Office - Microsoft Access Chapter 1

Relational Database

  • Uses multiple tables
  • Example of one-to-many table: one publisher owns many books

Exploring Microsoft Office - Microsoft Access Chapter 1

Relationships Window

Table Names

One To Many
Relationship

Field Names

Exploring Microsoft Office - Microsoft Access Chapter 1

Modifying Related Information

Clicking plus

sign displays all employees at L04

Modifying the address information for Location L04 changes the information for all individual employees at L04

Exploring Microsoft Office - Microsoft Access Chapter 1

Displaying Related Information

Related information is easily grouped together when creating reports

Exploring Microsoft Office - Microsoft Access Chapter 1

Agenda

  • Introduction
  • Tables and Forms
  • Reports and Queries
  • Relationships and Charts
  • Referential Integrity and Forms
  • Many-to-Many Relationships

Exploring Microsoft Office - Microsoft Access Chapter 1

Table Design Basics

  • Include necessary data

  • Store data in its smallest parts

  • Avoid calculated fields

Exploring Microsoft Office - Microsoft Access Chapter 1

  • Word Wrap - text automatically wraps text from one line to the next without pressing the enter key
  • Hard Return - created by the user when the enter key is pressed
  • Soft Return - created by the word processor as it wraps text from one line to the next
  • Insertion Point - Flashing vertical line that marks the place where text will be entered
  • Toggle Switch - A switch that causes the computer to alternate between two modes. For example, Caps Lock alternates between uppercase and lower case.
  • Insert Mode - Moves existing text to the right as new characters are being added. Insert key toggles between insert and overtype mode.,
  • Overtype Mode - Types over existing characters as new characters are added. Used for correcting typos.
  • Normal View - this view shows the only the body of the current page; faster and preferable when entering text and editing (See Figure 1.6 page 15 in textbook)
  • Page Layout View - this view shows the complete page with headers, footers and all formatting applied (See Figure 1.7 page 16 in textbook)
  • AutoCorrect Feature - corrects predefined spelling errors and/or mistakes in capitalization, automatically as the words are entered.

Creating a Table

  • Table Wizard is easiest
  • Designing a table yourself
  • Field names
  • Data types
  • Primary key
  • Views: Datasheet or Design
  • Properties

Exploring Microsoft Office - Microsoft Access Chapter 1

  • Word Wrap - text automatically wraps text from one line to the next without pressing the enter key
  • Hard Return - created by the user when the enter key is pressed
  • Soft Return - created by the word processor as it wraps text from one line to the next
  • Insertion Point - Flashing vertical line that marks the place where text will be entered
  • Toggle Switch - A switch that causes the computer to alternate between two modes. For example, Caps Lock alternates between uppercase and lower case.
  • Insert Mode - Moves existing text to the right as new characters are being added. Insert key toggles between insert and overtype mode.,
  • Overtype Mode - Types over existing characters as new characters are added. Used for correcting typos.
  • Normal View - this view shows the only the body of the current page; faster and preferable when entering text and editing (See Figure 1.6 page 15 in textbook)
  • Page Layout View - this view shows the complete page with headers, footers and all formatting applied (See Figure 1.7 page 16 in textbook)
  • AutoCorrect Feature - corrects predefined spelling errors and/or mistakes in capitalization, automatically as the words are entered.

New Table Screen

Click on ‘New’ Button

Select how to view/
create new table

Exploring Microsoft Office - Microsoft Access Chapter 1

Table Wizard Screen

Includes All Fields

Removes Selected Field

Exploring Microsoft Office - Microsoft Access Chapter 1

Table Design View

Primary

Key

Field Names

Field
Properties

Data Type

Description

Exploring Microsoft Office - Microsoft Access Chapter 1

Form Design View

Click and drag to
move Toolbox

Sizing handles

Exploring Microsoft Office - Microsoft Access Chapter 1

Form View

Current Record

Total Number of Records

Exploring Microsoft Office - Microsoft Access Chapter 1

Agenda

  • Introduction
  • Tables and Forms
  • Reports and Queries
  • Relationships and Charts
  • Referential Integrity and Forms
  • Many-to-Many Relationships

Exploring Microsoft Office - Microsoft Access Chapter 1

Report Types

  • Columnar report
  • Simplest type
  • Lists every field for every record in a single column
  • Tabular report
  • Displays selected fields in a row
  • One record per row

Exploring Microsoft Office - Microsoft Access Chapter 1

  • Word Wrap - text automatically wraps text from one line to the next without pressing the enter key
  • Hard Return - created by the user when the enter key is pressed
  • Soft Return - created by the word processor as it wraps text from one line to the next
  • Insertion Point - Flashing vertical line that marks the place where text will be entered
  • Toggle Switch - A switch that causes the computer to alternate between two modes. For example, Caps Lock alternates between uppercase and lower case.
  • Insert Mode - Moves existing text to the right as new characters are being added. Insert key toggles between insert and overtype mode.,
  • Overtype Mode - Types over existing characters as new characters are added. Used for correcting typos.
  • Normal View - this view shows the only the body of the current page; faster and preferable when entering text and editing (See Figure 1.6 page 15 in textbook)
  • Page Layout View - this view shows the complete page with headers, footers and all formatting applied (See Figure 1.7 page 16 in textbook)
  • AutoCorrect Feature - corrects predefined spelling errors and/or mistakes in capitalization, automatically as the words are entered.

Anatomy of a Report

  • Report header & footer
  • Page header & footer
  • Group header & footer
  • Detail section

Exploring Microsoft Office - Microsoft Access Chapter 1

Report Print Preview

Report
Header

Page Header

Detail Section

Exploring Microsoft Office - Microsoft Access Chapter 1

Report Design View (2 of 2)

Report
Header

Page Header

Detail Section

Page footer

Toolbox

Exploring Microsoft Office - Microsoft Access Chapter 1

Introduction to Queries

  • Query provides the resulting records from a question
  • Design grid: graphical tool used to develop queries
  • Dynaset: The records that satisfy the query criteria

Exploring Microsoft Office - Microsoft Access Chapter 1

Query Selection Criteria

  • AND condition
  • OR condition
  • Relational operators
  • Between function
  • NOT function
  • Wild card

Exploring Microsoft Office - Microsoft Access Chapter 1

Select Query Design View

Table

Run Button

Criteria

Exploring Microsoft Office - Microsoft Access Chapter 1

Select Query Dynaset

The results of the query are displayed as a dynaset

Exploring Microsoft Office - Microsoft Access Chapter 1

Crosstab Queries

Listing of

all Majors

Average GPA/Major by Gender

Exploring Microsoft Office - Microsoft Access Chapter 1

Agenda

  • Introduction
  • Tables and Forms
  • Reports and Queries
  • Relationships and Charts
  • Referential Integrity and Forms
  • Many-to-Many Relationships

Exploring Microsoft Office - Microsoft Access Chapter 1

Multiple-Table Queries

  • One-to-many relationship
  • Primary key
  • Foreign key
  • Referential integrity

Exploring Microsoft Office - Microsoft Access Chapter 1

  • Word Wrap - text automatically wraps text from one line to the next without pressing the enter key
  • Hard Return - created by the user when the enter key is pressed
  • Soft Return - created by the word processor as it wraps text from one line to the next
  • Insertion Point - Flashing vertical line that marks the place where text will be entered
  • Toggle Switch - A switch that causes the computer to alternate between two modes. For example, Caps Lock alternates between uppercase and lower case.
  • Insert Mode - Moves existing text to the right as new characters are being added. Insert key toggles between insert and overtype mode.,
  • Overtype Mode - Types over existing characters as new characters are added. Used for correcting typos.
  • Normal View - this view shows the only the body of the current page; faster and preferable when entering text and editing (See Figure 1.6 page 15 in textbook)
  • Page Layout View - this view shows the complete page with headers, footers and all formatting applied (See Figure 1.7 page 16 in textbook)
  • AutoCorrect Feature - corrects predefined spelling errors and/or mistakes in capitalization, automatically as the words are entered.

Get External Data

  • Get External Data command
  • Export command
  • Import Spreadsheet Wizard
  • Import Text Wizard
  • Importing versus linking

Exploring Microsoft Office - Microsoft Access Chapter 1

Multiple Table Query

Relationship

between tables

(one to many)

Each field &

table to display

Primary key
in Consultants
Table

Foreign key in
Clients Table

Exploring Microsoft Office - Microsoft Access Chapter 1

Total Query

Select Count from

drop-down menu

Click drop-down
arrow

Exploring Microsoft Office - Microsoft Access Chapter 1

Chart Wizard (1 of 6)

Select the table or query

for the report from the

drop-down menu

Select Chart Wizard from

the New Report dialog box

Exploring Microsoft Office - Microsoft Access Chapter 1

Chart Wizard (2 of 6)

Select the fields to be included in the chart

Inserts only the selected field

Inserts all fields

Removes only the selected field

Removes all fields

Exploring Microsoft Office - Microsoft Access Chapter 1

Chart Wizard (3 of 6)

Select the chart type

Description of the currently selected chart type

Exploring Microsoft Office - Microsoft Access Chapter 1

Chart Wizard (4 of 6)

Preview the chart

Exploring Microsoft Office - Microsoft Access Chapter 1

Chart Wizard (5 of 6)

Title the chart

Legend display

View the chart or design view

Exploring Microsoft Office - Microsoft Access Chapter 1

Chart Wizard (6 of 6)

Zoom

Chart from Wizard

Modified Y and X

axis now match

the query data

Exploring Microsoft Office - Microsoft Access Chapter 1

Agenda

  • Introduction
  • Tables and Forms
  • Reports and Queries
  • Relationships and Charts
  • Referential Integrity and Forms
  • Many-to-Many Relationships

Exploring Microsoft Office - Microsoft Access Chapter 1

Relationships Window

CustomerID is primary key

in Customers table

Relationship line

CustomerID is

foreign key in

Loans table

Enforce Referential

Integrity

Exploring Microsoft Office - Microsoft Access Chapter 1

Referential Integrity (1 of 2)

  • Access automatically implements a form of data validation during data entry
  • Can enter a record in the “one” table, but not in the “many” table if record contains an invalid value for the foreign key
  • This ensures related tables are consistent with one another.

Exploring Microsoft Office - Microsoft Access Chapter 1

Referential Integrity (2 of 2)

Delete Record button

Click + to display

related records

(subdatasheet)

Cannot delete a Customer without first deleting related Loan

Exploring Microsoft Office - Microsoft Access Chapter 1

Form Wizard (1 of 4)

Select the table
or query for
the form

Select all fields
to add to the form

Step 1

Step 2

Exploring Microsoft Office - Microsoft Access Chapter 1

Form Wizard (2 of 4)

Step 3

Select the table
or query for
the subform

Select all fields
to add to the subform

Exploring Microsoft Office - Microsoft Access Chapter 1

Form Wizard (3 of 4)

Step 4

Step 5

Choose how to display

the form and subform

Exploring Microsoft Office - Microsoft Access Chapter 1

Form Wizard (4 of 4)

Choose the style

Enter a name for the form and the subform

Step 6

Step 7

Exploring Microsoft Office - Microsoft Access Chapter 1

Subform

Customer

C01

Loans for

Customer C01

Status bar for

Loans form

Status bar for Customers form

Exploring Microsoft Office - Microsoft Access Chapter 1

Multiple Table Queries

  • Query information from two tables

  • Create a select query using both tables

  • Query window displays selected fields of each table

Exploring Microsoft Office - Microsoft Access Chapter 1

Multiple Table Queries

Column selector

Enter criteria

Select table

Exploring Microsoft Office - Microsoft Access Chapter 1

Multiple Subforms (1 of 2)

  • A main table can have multiple levels of subforms

  • Useful for displaying a one-to-many relationship within a one-to-many relationship

Exploring Microsoft Office - Microsoft Access Chapter 1

Multiple Subforms (2 of 2)

Customer C01

Loan L031 for

Customer C01

Payments for

Loan L031

Exploring Microsoft Office - Microsoft Access Chapter 1

Agenda

  • Introduction
  • Tables and Forms
  • Reports and Queries
  • Relationships and Charts
  • Referential Integrity and Forms
  • Many-to-Many Relationships

Exploring Microsoft Office - Microsoft Access Chapter 1

Many-to-Many Relationships

  • A many-to-many relationship requires an additional table that has a one-to-many relationship to each of the related tables

  • The primary key of the additional table is the combination of the primary keys of the related tables

Exploring Microsoft Office - Microsoft Access Chapter 1

Implementing Many-To-Many Relationships

Many-to-many relationship is implemented by a

pair of one-to-many relationships

Enforce Referential

Integrity is selected

Cascade Delete Related

Records is selected

Exploring Microsoft Office - Microsoft Access Chapter 1

Referential Integrity

  • Ensures records in related tables are consistent with one another

  • Prevents adding a record to a related table with an invalid foreign key

  • Prevents deleting a record in the primary table when there are corresponding records in the related table

Exploring Microsoft Office - Microsoft Access Chapter 1

Main Form and Subform

Main form has fields

from Orders and

Customers tables

Subform has fields from Order Details and Products tables

Exploring Microsoft Office - Microsoft Access Chapter 1

Designing a main and subform

Main form Detail

Subform Detail

Exploring Microsoft Office - Microsoft Access Chapter 1

Parameter Query

Enter the prompt in square brackets in the Criteria field

Users are prompted

with a dialog box when the

query is executed

Exploring Microsoft Office - Microsoft Access Chapter 1

Adding Tables

Existing tables and

relationships are unaffected

by addition of new table

Exploring Microsoft Office - Microsoft Access Chapter 1