slide1
Download
Skip this Video
Download Presentation
Lesson 1: Exploring Access 2007

Loading in 2 Seconds...

play fullscreen
1 / 141

Lesson 1: Exploring Access 2007 - PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on

Lesson 1: Exploring Access 2007. Lesson Objectives. After studying this lesson, you will be able to: Define what a database is Identify the types of objects modern databases contain Launch Access 2007 and identify elements of the application window Open a database and use the Navigation Pane

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Lesson 1: Exploring Access 2007' - waite


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lesson objectives
Lesson Objectives

After studying this lesson, you will be able to:

Define what a database is

Identify the types of objects modern databases contain

Launch Access 2007 and identify elements of the application window

Open a database and use the Navigation Pane

Open database objects, navigate within database objects, and close the objects

Close a database properly and exit Access 2007

defining access databases
Defining Access Databases

What Is a Database?

A collection of related data stored together in one electronic file

database structures
Flat Files

Object-Oriented Files

Database Structures

Repetitive data

launching access 2007
Launching Access 2007

Same basic procedures used to launch other computer programs

exploring the access environment
Exploring the Access Environment

Create a new blank database

Lists of templates

Recently used database list

opening a database
Opening a Database

Same basic procedures used to open files in other computer programs

Enabling Content to control security

identifying elements of the access window
Identifying Elements of the Access Window

Ribbon tabs

Shutter bar Open/Close button

Navigation Pane

Status bar

object types
Object Types
  • Access 2007 databases contain four basic types of objects:
    • Tables – hold the data
    • Queries – search the table data
    • Forms – input the table data
    • Reports – report the table data as meaningful information
  • Each object type is represented by a different icon:
    • Tables –
    • Queries –
    • Forms –
    • Reports –
using the navigation pane
Using the Navigation Pane
  • Shutter button opens and closes the Pane
  • Organization appears in the Pane banner
  • Pane banner menu button ▼ displays the Navigation Pane menu
  • Choose a different organization from the Pane menu
  • Expand and collapse buttons to show/hide each object list
opening navigating and closing database objects
Opening, Navigating, and Closing Database Objects

Each database object is listed on the Navigation Pane

To open any object, double-click the object name

Default setting for Access 2007 displays each object as a separate tabbed page in the Access window work area

working with tabbed objects
Working with Tabbed Objects
  • To switch from one object to another, click the tabs
navigating datasheets
Navigating Datasheets
  • Tables and queries display in a row and column layout called a datasheet
  • Datasheet resembles an Excel spreadsheet
  • To move from column to column, press Tab or Enter or click the cell
lesson objectives1
Lesson Objectives

After studying this lesson, you will be able to:

Add records to a database using both tables and forms

Change the layout of a table by adjusting column width, hiding columns, and rearranging column layout

Locate and update records by sorting, filtering, and using Find and Replace

Preview and print database data

Save a database as a new file

adding records using a table
Adding Records Using a Table

The primacy of tables

Tables hold raw data

All reports, queries, and forms use table data

Two basic input tools for adding data to database tables:

Typing data into the Datasheet view of the appropriate table

Typing data into a form associated with the appropriate table

fields records and files
Fields, Records, and Files
  • Fields
    • Basic unit of data represented by a column in a table datasheet
  • Record
    • Group of related fields or all fields related to a specific item: person, place, or thing
    • Each row of a datasheet represent a record
  • File
    • Group of related records represented by a table in a database
saving records
Saving Records

Access automatically saves each record as you complete it

Edits made to records are also automatically saved

autonumber fields
AutoNumber Fields

Fields that automatically number records as you add them to a table

It is unnecessary to type a number in these fields

Numbers are un-editable

The number is assigned to the specific record

If you delete a record from a table, Access never assigns the number to any new record

entering data into a table datasheet
Entering Data into a Table Datasheet

The new row is identified with an asterisk

Press (Tab) or (Enter) to move to the next column

Click the New Record button

changing the width of columns
Changing the Width of Columns

Drag a column border to make the column on the left of the border wider or narrower

Double-click a column heading border to change the width of the column on the left to fit the longest data entry in the column

Before

After

moving and hiding data columns
Moving and Hiding Data Columns

Hiding and moving fields in a datasheet has no impact on the actual structure of the table

Data remains available but is just hidden from view when columns are hidden

Moving columns enables you to print datasheets in different arrangements

The E-mail Address column is empty

The column is hidden

saving a table layout
Saving a Table Layout

Each time you make a change to the layout of a table you must save it

The Save button appears on the Quick Access toolbar

adding records using a form
Adding Records Using a Form

Same basic procedures are used to add records using a form

Data entered into a form is added to the associated table

Press (Tab) or (Enter) in a form to move from field to field

retrieving data
Retrieving Data

Sorting features

Filtering tools

Find and Replace

sorting records
Sorting Records

Sort Ascending

Alphabetical order from A to Z

Numeric order from lowest to highest

Chronological order from first to last

Sort Descending

Reverse alphabetical order from Z to A

Numeric order from highest to lowest

Reverse chronological order from last to first

sorting records using multiple fields
Sorting Records Using Multiple Fields

Access considers second fields when values in the first field are equal

Example: Personal Names

When the last name is the same, you can tell Access to consider the first name

Last name is the primary sort field

First name is the secondary sort field

how multiple column sorts work
How Multiple Column Sorts Work

Access sorts data on multiple fields from left to right

Columns in a table must appear side by side in the datasheet

The column on the left must be the one you want sorted first (primary sort field)

using find and replace
Using Find and Replace

Techniques similar to those in other applications

Use Find and Replace to locate records to remove or edit records

filtering records
Filtering Records

Filter by Selection

Selects records on the basis of the value contained in the active field for the active record

Filter by Form

Selects records on the basis of values or conditions (criteria) that you type in one or more form fields

Again, Access searches only the fields you specify to find the match.

using the toggle filter tool
Using the Toggle Filter Tool

After you apply a filter, clicking the Toggle Filter button removes the filter and displays all records

After removing a filter, clicking the Toggle Filter button re-applies the last filter applied

A ToolTip displays to let you know what action you are performing: Remove Filter or Apply Filter.

identifying logical operators
Identifying Logical Operators

Identifies the comparison Access should perform

Operators include:

=

>

<

<>

>=

<=

using wildcards
Using Wildcards

Used in place of specific characters

Primary wildcards used:

* Represents any number of characters

? Represents an individual character

Examples:

*Graham* locates all records with graham within the text

Gra?am locates all records with gra at the beginning of the field value and am at the end of the field value with only one letter between

using the save as command
Using the Save As Command

The Office  Save As command is used to save the active object rather than the file

Display the Save As menu to save the entire database as a new file

lesson objectives2
Lesson Objectives

After studying this lesson, you will be able to:

Create a new blank database

Create a database table using Datasheet View

Create a form

Add records to a table

Create a report

Create a simple query

Create a table from an Excel worksheet

Create a new database using a template

planning a database
Planning a Database

What information do you want to be able to obtain?

What data elements (fields) must you include to be able to obtain the information?

What types of data will you enter into each field?

Dates, numeric values, amounts of money, text, etc.

What fields of data relate to the same basic items and could be grouped together?

How do the groups of data relate to each other?

What is the most efficient way to get data into the database tables?

What questions will you need the database to be able to answer?

creating a new database
Creating a New Database

Database – Shell that holds all data items

Formats:

Blank

Template

Sample

Must save a new database before adding items to it

Access creates a blank, unnamed table after you save the new database

Generic Field# names appear as column headings in the default table Access creates

guidelines for creating tables
Guidelines for Creating Tables

Each table should contain fields that focus on data specific to one type of item

Such as customers, employees, inventory, etc.

Each table should have a primary key that cannot be empty

Each generic field# heading is replaced with the official field name

creating additional database objects
Creating Additional Database Objects

Create tab of the Ribbon contains tools for creating all database objects

Tools on the Ribbon are grouped by object type

Multiple tools are available for each object

creating simple forms input
Creating Simple Forms—Input
  • Simple forms contain all table fields
  • Access arranges fields appropriately on the form and uses a default form design
creating simple reports output
Creating Simple Reports—Output
  • Processes data into meaningful information
  • Formats data for printing
  • Creates a report containing all fields from the associated table
creating queries using a wizard
Creating Queries Using a Wizard

Steps you through the process of creating a query

Enables you to select specific fields to display in a datasheet

Enables you to add criteria for selecting specific records

importing data
Importing Data
  • Creates new tables from data stored in other programs
  • External Data tab on the Ribbon contains Import tools
  • Most import procedures use Wizards
using templates to create databases
Using Templates to Create Databases

In Access, database templates are ready-to-use databases

Each template contains a set of objects designed for a specific purpose

Storing contacts, tracking projects, etc.

Each object in the database created with a template can be customized to meet specific needs

lesson objectives3
Lesson Objectives

After studying this lesson, you will be able to:

Create tables using Table Design View

Append records to a database table

Create and run queries using Query Design View

Create forms and reports using wizards

Copy and save database objects as new objects

Back up a database

Compact and repair a database

using table design view
Using Table Design View
  • Presents a blank palette on which to enter fields
  • Provides tools for setting data types, descriptions, and properties
  • Enables you to change the structure and organization of fields in the table
switching views
Switching Views
  • Button on the Home tab, Views group of the Ribbon
  • Button displays an icon that identifies the view you will display if you click the button
  • The button menu enables you to switch to different views
entering field names
Entering Field Names
  • Field names:
    • Short but descriptive
    • Can contain alpha-numeric characters
    • Can contain many symbols
    • Can contain spaces
    • Must start with a character or symbol
identifying data types
Identifying Data Types
  • Data types identify the type of data to be added to a field, such as
    • Text
    • Numbers
    • Dates/Times
    • Currency
  • Setting a data type controls values that the field contains
adding field descriptions
Adding Field Descriptions
  • Help identify the format of data
  • Provide valuable documentation for maintaining a database
  • Describe the field contents
creating queries using query design
Creating Queries Using Query Design
  • Enables you to place fields from multiple tables to display in a single datasheet
  • Provides controls for setting criteria based upon values in multiple fields
  • Enables you to show or hide fields containing criteria
the query design grid
The Query Design Grid

Table

Field name

Table name

Sort options

Criteria

Show box

Or criteria line

add fields to the design grid
Add fields to the Design Grid
  • Several techniques
    • Double-click a field name in the table list
    • Drag a field to the grid
    • Select the field from the Field list in the grid
run queries to view results
Run Queries to View Results
  • Edit data in recordsets to edit data in table
  • Sort query results in ascending or descending order
  • Sort with multiple fields from left to right
using criteria to select records
Using Criteria to Select Records
  • Equality criteria matches records exactly
using criteria to select records1
Using Criteria to Select Records
  • Comparison criteria matches records in a range
using wizards to create objects
Using Wizards to Create Objects
  • Enables you to include fields from multiple tables
  • Steps present options for you to choose
  • Similar to using wizards in other applications
tools for managing databases
Tools for Managing Databases
  • Tools on the Office menu
  • Help ensure the smooth operation of a database
  • Back up a database
  • Compact and repair a database
modifying table structure
Modifying Table Structure
  • Includes:
    • Changing field names
    • Changing data types
    • Adding descriptions
    • Assigning Primary Key fields
    • Setting field properties
  • Can impact data—causing data loss
setting lookup fields
Setting Lookup Fields
  • Creates a list of valid values to use during data entry
  • Reduces time required for data entry
  • Reduces errors associated with data entry
  • Restricts data to valid entries
  • Create lookups using:
    • Lookup wizard
    • Lookup settings on the Lookup tab in Table Design View
setting field properties
Setting Field Properties
  • Control the way data displays
    • Accounts for different format (all caps, lowercase, etc.) used by data entry clerks
    • Ensures consistent look in reports and other objects
  • Control values that can be entered
  • Provide tips for data entry
  • Set the number of characters that can be entered for a field
  • Set a default field value
setting field size
Setting Field Size
  • Limits the number of characters allowed for field values
  • Triggers a warning message that data may be lost
  • Example:
    • Setting the State field size to 2 to accommodate state abbreviations deletes all values containing more than 2 characters in the State field
setting captions
Setting Captions
  • Changes the text that appears in datasheet column heading
  • Presents more descriptive field titles

Fname  First Name

setting input masks
Setting Input Masks
  • Sets data format
  • Provides a consistent display of data for all records
  • Uses many symbols to control format
  • Access adds characters that control how data displays:

1234567890(123) 456-7890

howard SMITHHoward Smith

creating validation rules
Creating Validation Rules
  • Limits values entered into a field
    • Reduces errors associated with data entry
    • Restricts data entry to valid values
  • Data type should be set for the type values entered
  • Text provides instructions for data entry
    • Appears in status bar when field is active
    • Appears in message box when invalid values are entered
  • Wide variety of comparison operators
  • Wildcards can be used
analyzing and documenting databases
Analyzing and Documenting Databases
  • Performance Analyzer
    • Reviews structures of database objects
    • Identifies potential trouble spots/objects
    • Reports the results of the analysis
    • Ensures smooth functioning of the database
  • Database Documenter
    • Reviews all database objects
    • Documents all objects and their associations
    • Includes field names and other significant information
objectives
Objectives
  • After studying this lesson, you will be able to:
    • Modify table field settings
    • Create lookup fields that return multiple values
    • Create a split form
    • Create and print relationships among tables
    • Identify object dependencies
principles of good database design
Principles of Good Database Design
  • Reduce redundancy
    • Divide data into subject-based tables
  • Show Access how data in all tables related to scope (purpose) of the database
  • Ensure that data is complete and accurate
  • Include report formats, forms, and queries to process data and report information that meets the intended needs of the database

Incomplete/inaccurate data results in inaccurate processing and reporting, and invalidates the integrity of the database.

normalizing data
Normalizing Data

Process of refining tables, keys, fields, and relationships to create an efficient database

Includes:

Discarding repeating groups

Minimizing redundancy

Eliminating composite keys for partial dependency

Separating non-key attributes

Follows steps and guidelines for designing a database

working with relationships
Working with Relationships

Three types of relationships:

One-to-One Relationship

One-to-Many Relationship

Many-to-Many Relationship

one to one relationship
One-to-One Relationship

Each record in Table A can have only one matching record in Table B

Example: Each employee’s address is stored in one table and has a matching record in the payroll table

one to many relationships
One-to-Many Relationships
  • Each record in Table A can have many matching records in Table B
    • Example: Each supplier in the Suppliers table could have multiple items in the Inventory table
many to many relationships
Many-to-Many Relationships
  • Records in Table A can have many matching records in Table B, and vice-versa
  • Normally requires a Junction Table
    • Example: Items could be ordered by many customers and each customer might order many items
relationship join line
Relationship Join Line

Connects fields common to both tables

referential integrity
A system of rules used to ensure that relationships are valid

Must be set in Edit Relationships dialog box

Access identifies the many and one side of the relationship

Referential Integrity
cascade options
Cascade Options

Set in the Edit Relationships dialog box

Cascade Update updates key field of a related table when edits are made to field in primary table

Cascade Delete removes records from a related table when record is deleted from primary table

object dependencies
Shows the object for which dependencies are shown at the top

You can choose to identify object dependent on the active object or those on which the object depends

Objects are listed by type

Object Dependencies
objectives1
Objectives
  • After studying this lesson, you will be able to:
    • Design a query using multiple tables
    • Set query criteria
    • Define a query sort order
    • Create and format a calculated field
    • Use functions in query expressions
    • Create a crosstab query
    • Create unmatched and duplicates queries
setting query criteria

Wildcard

Dates

Comparison Operator

Value

Wildcard

Setting Query Criteria
setting and and or criteria
Setting AND and OR Criteria
  • AND Criteria
  • OR Criteria
pemdas please excuse my dear aunt sally
PEMDASPlease Excuse My Dear Aunt Sally

(2+2)*32-6/2=33

  • Parentheses
  • Exponentials
  • Multiplication/Division
  • Addition/Subtraction
calculated fields
Calculated Fields

Have no value of their own

Perform calculations using other field values

Field names from

existing tables

Arithmetic or comparison operator

Calculated field name

crosstab queries
Crosstab Queries
  • Rearranges data for summarizing
  • Groups data and totals values
objectives2
Objectives
  • After studying this lesson, you will be able to:
    • Use Form Design View and Layout View
    • Add, delete, format, and modify form controls
    • Apply AutoFormats to forms
    • Set form tab order
    • Create a multiple item form
    • Print forms
customizing forms in design view
Customizing Forms in Design View

Control tools for adding new controls

Image

Form header

Detail section

Bound control

Control label

Coloredbackground

Control text box

Title

using control handles
Using Control Handles

Large handles are used to move the individual

parts of the control—label or text box—separately

Sizing handles

The mouse appears as a white arrow

with a four-headed black arrow to move

working with controls
Working with Controls
  • Select controls with:
    • (Shift) + click
    • Selection box
    • Ruler
customizing forms in design view1
Customizing Forms in Design View
  • Restrict data entry in forms to ensure data integrity
    • Use Properties dialog box
    • Enabled controls receive focus (you can click in them) and data (you can type in them)
    • Locked controls cannot receive focus and cannot receive data
applying conditional formatting
Applying Conditional Formatting

Format for values

more than $1,000

Format for values less than $500

Format for values

between $500 and $1,000

sizing form sections
Sizing Form Sections

Drag the section bar below the section (Detail bar, in this case) down to make the Form Header section larger

The mouse pointer appears

as a two-headed arrow

setting a form tab order
Setting a Form Tab Order
  • Enables you to arrange controls on a form in an appropriate order, and then change the default order Access moves from one to the next when you press Tab

Field names in order you want to access them using Tab

objectives3
Objectives
  • After studying this lesson, you will be able to:
    • Create a report using Report Design View
    • Modify reports
    • Add report sorting and grouping levels
    • Add graphics to reports and apply AutoFormats
    • Add a calculated control to a report
    • Create controls to calculate totals
report design view features
Report Design View Features

Control labels normally appear as column headings in reports

Control text boxes normally appear in the Detail section of reports

adding controls to reports
Adding Controls to Reports

Drag field names from the Field List panel

Add controls using Control tools on the Ribbon

Arranging controls

Select the control label and cut it from the Detail section and paste the label in the Page Header section

Arrange controls by dragging

Arrange controls with alignment tools

setting grouping levels
Setting Grouping Levels

A group is a collection of records that has at least one data element in common

Set grouping levels using the Group, Sort, and Total Panel

working with group
Working with Group
  • Grouping records places a Group Header and Group Footer section on the report
  • Field controls are added to the Group Header or Group Footer
  • Access enables multilevel grouping
  • Sort order can be set for groups
performing calculations
Performing Calculations
  • Use many of the same techniques used to add calculations to forms
  • The Expression Builder is available for creating report calculations
  • Groups can be totaled using the Total feature on the Group, Sort, and Total panel
  • Aggregate Fields can be set to sum, average; perform other standard calculations
creating labels
Creating Labels
  • Arrange fields on a report palette using a Wizard
  • Techniques and label layout similar to adding field codes to Word documents for mail merges

Fields positioned appropriately with spacing and lines set up

Available fields

objectives4
Objectives
  • After studying this lesson, you will be able to:
    • Create a query
    • Identify relationship join properties
    • Create and run parameter queries
    • Create a calculated field in a query
    • Create and run action queries
access query types
Access Query Types
  • Select Query
  • Crosstab Query
  • Unmatched Query
  • Duplicates Query
  • Parameter Query
  • Action Query
  • SQL Query

working with relationships1
Working with Relationships
  • When relationships are missing, querying a database using fields from multiple tables creates unexpected, meaningless results
  • These results are called Cartesian products
  • Each record is listed multiple times
relationship join properties
Relationship Join Properties
  • Join Properties
    • Control circumstances under which a record from a related table appears when you run a query
  • Inner Joins
    • Check for records with matching values in the join field and displays only those that match
  • Left Outer Joins
    • Display all records from the one side of a one-to-many relationship
  • Right Outer Joins
    • Display all records from the many side of a one-to-many relationship
join properties dialog box
Join Properties Dialog Box

Right table name: Many side of one-to-many relationship

Left table name: One side of one-to-many relationship

Join types and descriptions

one vs many sides of relationships
One vs. Many Sides of Relationships

Many (right) side of relationship

One (left) side of relationship

Join Type button

parameter queries
Parameter Queries
  • Prompt users for input to narrow the query output
  • Criteria expression contains prompt text

Criteria expression prompts user for a value when the query is run

Criteria expression appears in the query grid Criteria row

Brackets always surround the criteria expression.

action queries
Action Queries
  • Delete Query
    • Deletes a group of records from one or more tables
  • Update Query
    • Makes global changes to a group of records in one or more tables
  • Append Query
    • Adds a group of records from one or more tables to the end of one or more tables
  • Make-Table Query
    • Creates a new table from all or selected data in one or more tables
setting up an action query
Setting Up an Action Query
  • Each action query has a source and destination table
  • Action types are set using Query Type controls on the Ribbon

Action query types

Source table is the active table; destination table identified in the dialog box

objectives5
Objectives
  • After studying this lesson, you will be able to:
    • Create a main form containing a subform
    • Edit a data source
    • Format a form and add a logo
    • Add a calculated field to a form
    • Add the current date to the form
    • Disable and lock form fields
    • Add tips to controls
    • Create a pop-up form
    • Hide form screen elements
main forms and subforms
Main Forms and Subforms

Add a subform to a form to display related data from another table

Related data from another table appears in Table view

Related data from another table appears as a subform in the form

adding subforms to forms
Adding Subforms to Forms
  • Use the Form Wizard
  • Use the Subform control on the Ribbon
editing a record source
Editing a Record Source
  • A record source is the database object from which a form or report obtains its data
  • The record source is identified in the Properties box and can be edited

Record source from which the form obtains its data

creating autoformats
Creating AutoFormats
  • AutoFormats
    • Predesigned layout and color design you can save and apply to other forms and reports
  • Access comes with a built-in set of AutoFormats
  • Modify or create additional AutoFormats using forms you design
controlling data entry
Controlling Data Entry
  • Properties: Set to limit or prevent data entry and edit
    • Disable property: Displays fields grayed out and unavailable
    • Locked property: Displays fields as available but prevents data editing

Disabled field

Locked field

all about pop up forms
All About Pop-Up Forms

Display as pop-up windows that stay on top of other open database objects so they can be used as references to provide information

Pop-up forms appear as separate windows rather than tabbed forms

Modeless pop-up forms sit on top of other objects so that you can continue to work

objectives6
Objectives
  • After studying this this lesson, you will be able to:
    • Import a report into a database
    • Add a subreport to a main report
    • Create a report from a subreport
    • Number items in a report
    • Create calculated controls on a subreport
    • Set page breaks and customize controls
    • Analyze report performance
smart use of tags record source
Smart Use of Tags – Record Source
  • SmartTags provide a shortcut to editing a record source

SmartTag

Command on shortcut menu to edit record source

adding subreport to a report
Adding Subreport to a Report
  • Techniques used are similar to adding subforms to main forms
    • Use the Report Wizard
    • Use the Subreport control on the ribbon
  • Use an existing report or form as a subreport
  • Use a table or query as a subreport
saving a subreport as a report
Saving a Subreport as a Report
  • Forms added to reports as subreports can be saved as reports
  • Then add additional controls, such as numbering, to the report
numbering items in a report
Numbering Items in a Report
  • Numbering can be set using a Text Box control

Items are numbered

Control Source sets numbering

Running Sum identifies which records Access numbers and when numbering starts

setting special properties controls
Setting Special Properties & Controls
  • Can Grow Property
    • Sizes the subreport control to fit the data it contains—varies the size from record to record
  • Can Shrink Property
    • Reduces the size of the control to fit the data
  • Page Break Control
    • Enables you to force report pages
ad