Introduction to

MS Access 2007
Instructor:
ATIF FAQIRM
In Computer
Science

1 Copyright Crespo Technology Services Group, Inc.

 Course Outline
 Lesson 1: Exploring the Microsoft® Office  Lesson 4: Managing Data in a Table
Access™ 2007 Environment  Modify Table Data
 Examine Database Concepts  Sort Records
 Explore the User Interface  Work with Subdatasheets
 Explore the Ribbon  Lesson 5: Querying a Database
 Customize the Access Environment  Filter Records
 Obtain Help  Create a Query
 Use an Existing Access Database  Add Criteria to a Query
 Lesson 2: Designing a Database  Add a Calculated Field to a Query
 Describe the Relational Database Design Process  Perform Calculations on a Record Grouping
 Define Database Purpose  Lesson 6: Designing Forms
 Review Existing Data  View Data Using an Access Form
 Determine Fields  Create a Form
 Group Fields into Tables  Modify the Design of a Form
 Normalize Data

 Lesson 7: Generating Reports
Designate Primary and Foreign Keys
  View an Access Report
Determine Table Relationships
 Create a Report
 Lesson 3: Building a Database  Add a Custom Calculated Field to a Report
 Create a New Database  Format the Controls in a Report
 Create a Table  Apply an AutoFormat to a Report
 Manage Tables  Prepare a Report for Print
 Create a Table Relationship
 Save a Database as a Previous Version

2 Copyright Crespo Technology Services Group, Inc.

 Exploring the Microsoft® Office
Access™ 2007 Environment

3 Copyright Crespo Technology Services Group, Inc.

 Database Concepts
 Terms
 Database: collection of objects that allow you to store data,
organize it and retrieve it in any way you want. It may contain:
 Tables: structure that allow you to organize the data so that it's easy to
find later
 Forms: let you input the data into the tables
 Reports: print selected information from the tables
 Relational Database: stores data in various interrelated tables
where data is stored, accessed and presented without affecting
the structure

4 Copyright Crespo Technology Services Group, Inc.

 Database Concepts

Last Name First Name Student ID City

Pérez Juan 101010 Utuado

Crespo Carlos 802-91-2515 Aguadilla

5 Copyright Crespo Technology Services Group, Inc.

 Database Concepts
 Database Design
 Reason for the database: There are usually two reasons for a
database:
 Data management: Add, remove or modify data
 Data analysis: Analyze information and trends
 Database content

6 Copyright Crespo Technology Services Group, Inc.

 Explore the User Interface
 MS Access 2007 is a Database Management System
 It allows the user to:
 Retrieve and update data
 Present data sorted or grouped
 Import or export data from various sources including the
Internet

7 Copyright Crespo Technology Services Group, Inc.

 Explore the User Interface
 The user interface is composed of:
 Office button
 Quick access tool bar
 The ribbon
 The navigation pane
 The MS Office window frame
 Dialog box launcher

8 Copyright Crespo Technology Services Group, Inc.

 Explore the User Interface

9 Copyright Crespo Technology Services Group, Inc.

 Explore the User Interface

10 Copyright Crespo Technology Services Group, Inc.

 Explore the Ribbon

 Home Tab
 Views
 Clipboard
 Font
 Rich Text
 Records
 Sort & Filter
 Find

11 Copyright Crespo Technology Services Group, Inc.

 Explore the Ribbon

 Create Tab
 Tables
 Forms
 Reports
 Other

12 Copyright Crespo Technology Services Group, Inc.

 Explore the Ribbon

 External Data
Tab
 Import
 Export
 Collect Data
 SharePoint
List

13 Copyright Crespo Technology Services Group, Inc.

 Explore the Ribbon

 Database
Tools Tab
 Macro
 Show/Hide
 Analyze
 Move Data
 Database
Tools

14 Copyright Crespo Technology Services Group, Inc.

 Use an Existing Database
 Tables are composed of
three elements
 Fields: these are the Student ID Last First Name
Name
table columns 101010 Pérez Juan
 Records: these are the 802-91-2154 Crespo Carlos
table rows 010101 Campos Diana
 Values: A single piece
of data

15 Copyright Crespo Technology Services Group, Inc.

 Use an Existing Database
 Queries
 Request Information
 Requires fields and
table names
 Can include conditions
 Can be reused
Student ID Last First Name
 Can perform
Name
101010 Pérez Juan
calculations
802-91-2154 Crespo Carlos
010101 Campos Diana

16 Copyright Crespo Technology Services Group, Inc.

 Use an Existing Database
Student ID Last First Birth Dept. City
Name Name Date
101010 Pérez Juan 01/01/70 HUM Utuado
Last Name First Name Dept.
802-91-2154 Crespo Carlos 07/16/73 IS Aguadilla
Crespo Carlos IS
010101Campos
Campos Diana Diana
03/20/78 IS IS Lares
123456 Figueroa Ricardo 10/31/76 ADM Coamo
131313 Rivera David 11/02/80 ADM Utuado

 Recordset
 Is a table
 Displays smaller set of records
 Can come from a table or query

17 Copyright Crespo Technology Services Group, Inc.

 Use an Existing Database
 Forms
 Used to display and
edit data
 Based on tables or
queries
 Can be customized
 Can include
 Calculations
 Graphics
 Other objects

18 Copyright Crespo Technology Services Group, Inc.

 Use an Existing Database
 Report
 Screen output of data
 Presented as specified
by the user
 Data comes from tables
or queries
 Can perform
calculations and display
results
 Can be printed

19 Copyright Crespo Technology Services Group, Inc.

 Customize the Access Environment

 Can be found on the MS

Office button

20 Copyright Crespo Technology Services Group, Inc.

 Customize the Access Environment

 Various options to
customize the MS Access
environment
 Database objects
 Security
 Quick Access toolbar

21 Copyright Crespo Technology Services Group, Inc.

 Obtain Help

 Access Help
 Complete manual on
features
 Quick and easy access to
answers

22 Copyright Crespo Technology Services Group, Inc.

 Design a Database

23 Copyright Crespo Technology Services Group, Inc.

 Relational Database Designing Process
 Sequential process for developing a database plan
 This process consists of seven steps
 Database purpose
 Review existing data
 Create list of fields
 Organize fields into tables
 Enter sample data and verify if changes are required
 Designate primary and foreign keys
 Determine table relationships

24 Copyright Crespo Technology Services Group, Inc.

 Define Database Purpose
 Statement of purpose
 Defines the scope of a database
 Helps guide the design
 Implies the kind of data
 Determine the probable users

25 Copyright Crespo Technology Services Group, Inc.

 Review Existing Data
 Relevant data to the statement of purpose
 Can be in paper or electronic
 Paper
 Internal Business Forms
 Government Forms
 Invoices
 Electronic
 Spreadsheets
 Word documents
 Databases (Access, MS SQL, Oracle, etc.)
 Web pages

26 Copyright Crespo Technology Services Group, Inc.

 Determine Fields
 The required fields depend on the information required by
the user
 Reports
 Summary information

27 Copyright Crespo Technology Services Group, Inc.

 Group Fields Into Tables
 Business Rules
 Policies and procedures that govern operation of an
organization
 Define methods for data entry in a database
 Define a standard naming convention for objects. Example:
Student name object can be S_Fname and the lastname can be
S_Lname

28 Copyright Crespo Technology Services Group, Inc.

 Database Design
 Class Exercise
 You are required to develop a database to keep track of the
books being lent by the university library.
 Assumptions
 Only students are allowed to take books
 The books can only be lent for two weeks
 No other service will be tracked through this database

29 Copyright Crespo Technology Services Group, Inc.

 Database Design
 Class Exercise
 Develop a list of data required for this database
 How many tables are required? Why?
 What information is required for each table?

30 Copyright Crespo Technology Services Group, Inc.

 Normalize Data
 Normalization
 Process organizing data in a database
 Optimizes table structure
 Refining database by reducing complex data into simple table
structures
 Optimizes by
 Eliminate redundancy
 Eliminate inconsistency dependency between tables

31 Copyright Crespo Technology Services Group, Inc.

 Normalize Data
 Break data into smallest values possible
 Assure data is not repeated
 Break repeated data into separate tables
 Avoid empty fields as much as possible
 Consistent references in tables (naming convention)

32 Copyright Crespo Technology Services Group, Inc.

 De-normalization
 Definition
 Combining tables into a bigger table
 Uses
 Make queries run faster
 Keep similar data together
 Keep table structure simple

33 Copyright Crespo Technology Services Group, Inc.

 Designate Primary and Foreign Keys
 Primary Key
 Field with unique values
 Values identify each record
 Used to create relationships between tables
 Can be of any data type except memo, OLE or Attachment
 Can never be blank
 Values do not change or have duplicates

34 Copyright Crespo Technology Services Group, Inc.

 Designate Primary and Foreign Keys
 Composite Key
 Made of the union of two unique fields
 Foreign Key
 Field that relates to primary key of another table
 Primary key acts as record index
 Helps implement relationship between tables
 Foreign key helps establish relationship with primary key

35 Copyright Crespo Technology Services Group, Inc.

 Designate Primary and Foreign Keys
 Class Exercise
 Define a primary and foreign key for each table. This field
identifies each individual in a unique way.
 List any field that contains a limited list of items. Example:
City or state.
 Define default values when possible. Example: 90% of students
will be from PR.

36 Copyright Crespo Technology Services Group, Inc.

 Determine Table Relationships
 Representation of association among data contained within
tables
 Helps poll matching fields
 One-to-one relationship
 Primary key and foreign key are unique
 One-to-many relationship
 Primary key unique with many foreign key values

37 Copyright Crespo Technology Services Group, Inc.

 Building a Database

38 Copyright Crespo Technology Services Group, Inc.

 Create a Database
 To create a database
 Open Access 2007
 In the Getting Started
screen select blank
database
 Write database name and
select location.
 Press Create

39 Copyright Crespo Technology Services Group, Inc.

 Create a Table
 Access creates a new table
when a new blank database
is created
 New tables can be created
by selecting the table icon
on the Create tab

40 Copyright Crespo Technology Services Group, Inc.

 Table Views
 Tables can be viewed in
several ways. The two
most common views are:
 Datasheet: This is the
default view and presents
record values
 Design: This presents the
table and field properties
but does no present record
values

41 Copyright Crespo Technology Services Group, Inc.

 Field Insertion Feature

 Allows adding new fields

from datasheet view
 Write the field name in the
column header
 Repeat for each field

42 Copyright Crespo Technology Services Group, Inc.

 Data Types
 Text: Contains characters up to 255
 Memo: Advanced text field. Can hold up to 2 GB of data with rich
text formatting
 Numbers: Numeric values up to 16 bytes
 Date/Time: Stores and manipulates date and time values
 Currency: Monetary values
 AutoNumber: Automatically creates a numeric value with new
records. Are usually used as primary keys.
 Yes/No: Boolean information (true or false)
 Hyperlink: Stores web addresses
 Attachment: Add files to a record
 Multivalued: Can have more than one value

43 Copyright Crespo Technology Services Group, Inc.

 New Fields
 Fields can be added
through:
 Field insertion feature
 Design view
 Multivalued fields are
added through the Lookup
Wizard

44 Copyright Crespo Technology Services Group, Inc.

 Creating Tables
 Create the tables for the books circulation class exercise
 Use the datasheet view as well as the design view

45 Copyright Crespo Technology Services Group, Inc.

 Table Maintenance
 Tables can be:
 Deleted
 Renamed
 Exported
 Add a description in the Table Properties Dialog

46 Copyright Crespo Technology Services Group, Inc.

 Table Properties
 Allows for a detailed
description of the table
 Presents information about
the table
 Create date
 Modified date
 Allows to hide a table
 Allows for row tracking
when used by multiple
simultaneous users

47 Copyright Crespo Technology Services Group, Inc.

 Referential Integrity
 Process to ensure data validity across two tables
 It is enforced by ensuring values in a foreign field have
corresponding values in the primary key
 Referential integrity prevents the entering of invalid data
 Benefits of Referential Integrity
 Avoid data loss
 Avoid updating data

48 Copyright Crespo Technology Services Group, Inc.

 Referential Integrity
 Referential integrity between two table can be set if:
 Both tables are in the same database
 The matching field is a primary key in one table or has a unique
index
 The related fields have the same data type

49 Copyright Crespo Technology Services Group, Inc.

 Table Relationships
 In the Database Tools tab select
relationships
 If no relationship exists, Access
brings the Show Tables or
Queries dialog to add them to
the relationship
 Once added, drag the primary
key to the foreign key.
 This brings the Edit relationships
dialog
 Referential integrity can be
enforced here
 A line is drawn between tables
to indicate relationship

50 Copyright Crespo Technology Services Group, Inc.

 Table Relationships
 Create the table relationships for the books circulation class
exercise
 Enforce referential integrity as required

51 Copyright Crespo Technology Services Group, Inc.

 Save a Database in a Previous Version
 In the Office Button menu
select Save As
 There are several database
format available :
 Access 2007
 Access 2002-2003
 Access 2000

52 Copyright Crespo Technology Services Group, Inc.

 Managing Data in a Table

53 Copyright Crespo Technology Services Group, Inc.

 Modifying Data
 Users can:
 Search for records
 Replace Data in records
 Add records
 Delete Records
 Modify Records

54 Copyright Crespo Technology Services Group, Inc.

 Find Command

 Helps locate specific data.

 Has various find options

55 Copyright Crespo Technology Services Group, Inc.

 Replace Command

 Helps replace specific data with new data

 Has various replace options

56 Copyright Crespo Technology Services Group, Inc.

 Totals Features

 Provides simple math functions to the tables

57 Copyright Crespo Technology Services Group, Inc.

 Sorting Records
 Can sort data
 Ascending
 Descending
 The order is based on
 Numeric information
 Alphanumeric information

58 Copyright Crespo Technology Services Group, Inc.

 Subdatasheets
 Datasheet nested within
another datasheet
 Contains data related to the
first datasheet
 Subdatasheet presence is
indicated by + sign on the
row

59 Copyright Crespo Technology Services Group, Inc.

60 Copyright Crespo Technology Services Group, Inc.
 Managing Data
 Class Exercise
 Fill the created tables with at least 10 records each
 Delete at least 1 record from each table
 Modify at least 1 record from each table

61 Copyright Crespo Technology Services Group, Inc.

 Queriyng a Database

62 Copyright Crespo Technology Services Group, Inc.

 Querying a Database
 Looking for data in a database is difficult unless there is
mechanism for extracting and isolating is
 A database is effective when data can be retrieved as easily as
it was entered
 Queries greatly reduce the data retrieval time

63 Copyright Crespo Technology Services Group, Inc.

 Filter Feature
 Display only the records that
match your criteria
 Filter data based on the
values in a column
 Filter by:
 Text for the text data type
 Number filters for the
number data type
 Date filters for the date data
type
 Multiple filters can be applied
to a table at the same time.

64 Copyright Crespo Technology Services Group, Inc.

 Query Wizard
 Select Query Wizard
 Select type of query
 Select query fields
 Request query detail
 Add title. Click Finish
 Query result shown

65 Copyright Crespo Technology Services Group, Inc.

 Query Types
 Simple: Can select fields from multiple tables and queries
 Crosstab: Can perform simple calculations
 Find duplicates: Can find duplicate field values
 Find unmatched: Finds records in a table with no related
records on another table

66 Copyright Crespo Technology Services Group, Inc.

 Query Design Context Tab

 Query Design
 Results
 Query Type
 Query Setup
 Show/Hide

67 Copyright Crespo Technology Services Group, Inc.

 Query Object Views

 Design View
 SQL View
 Datasheet View
 PivotTable View
 PivotChart View

68 Copyright Crespo Technology Services Group, Inc.

 Show Tables Dialog Box
 The Show Tables dialog
box is found on the Query
Design Context Tab
 The user can select to
view:
 Tables
 Queries
 Both

69 Copyright Crespo Technology Services Group, Inc.

 Create a Query
 Class Exercise
 Create a query of students
 Create a query of books

70 Copyright Crespo Technology Services Group, Inc.

 Query Criteria

 Search condition to retrieve specific information

 These are used to compare criteria value with values in
the column
 Calculations can also be performed

71 Copyright Crespo Technology Services Group, Inc.

 Comparison Operators

 Symbols used to compare two values

 When set in criterion establishes results after
comparison
 The result is always a true or false

72 Copyright Crespo Technology Services Group, Inc.

 List of Comparison Operators
 = : Equals
 < : Less than
 <= : Less than or equal to
 > : Greater than
 >= : Greater than or equal to
 <> : Not equal to
 Between And : Within a range
 Is Null : Null values

73 Copyright Crespo Technology Services Group, Inc.

 Conditional Operators

 Test for the truth of a comparison

 The result is always a true or false
 Parenthesis can be used to change the order of
evaluation
74 Copyright Crespo Technology Services Group, Inc.
 List of Conditional Operators
 AND : True if both conditions are true
 Example: >2 AND <>10
 OR : True if either condition is true
 Example: <10 OR >100
 NOT : True if the single instance is not true
 Example: Not Between 2 and 10

75 Copyright Crespo Technology Services Group, Inc.

 Create a Query
 Class Exercise
 Create a query of students that have books out
 Create a query of books that are past due to be returned

76 Copyright Crespo Technology Services Group, Inc.

 Calculated Fields

 Values come from calculations with other fields

 Are not entered by the user but are created when query runs
 The values may change if the expression is changed

77 Copyright Crespo Technology Services Group, Inc.

 Arithmetic Operators

Arithmetic Description Example

Operator
+ Addition value1 + value 2

- Subtraction value1 - value 2

* Multiplication value1 * value 2

/ Division value1 / value 2

78 Copyright Crespo Technology Services Group, Inc.

 Expressions

 Values come from calculations with other fields

 Are not entered by the user but are created when query runs
 The values may change if the expression is changed

79 Copyright Crespo Technology Services Group, Inc.

 Expression Builder

 Allows users to select objects and use operators and

functions to build formulas
 These can be used in queries or reports

80 Copyright Crespo Technology Services Group, Inc.

 Property Sheet Pane

 Allows users to set properties to objects

 These properties can be of Structure, appearance and behavior
 Features include Description, Format, Decimal Places, Input
Mask, Caption and Mart Tags

81 Copyright Crespo Technology Services Group, Inc.

 Group by Functions

 Perform calculations on a group of values

 Can be used with any number of values and result in a single
value
 Aggregate functions are used to group records in all databases
82 Copyright Crespo Technology Services Group, Inc.
 Designing Forms

83 Copyright Crespo Technology Services Group, Inc.

 Designing Forms
 Datasheet view
 Can be difficult to view large amounts data
 Searching can be time consuming
 Can lead to errors by misreading a value
 Access forms
 Allows to view and edit one record at a time
 Is easier to view

84 Copyright Crespo Technology Services Group, Inc.

 Form Views

View Description
Design A static view that helps design a form.
Form A dynamic view data within the developed form
Layout An interactive and dynamic view that you can use to create a
form

85 Copyright Crespo Technology Services Group, Inc.

 Form Sections

 Perform calculations on a group of values

 Can be used with any number of values and result in a single
value
 Aggregate functions are used to group records in all databases
86 Copyright Crespo Technology Services Group, Inc.
 Group by Functions

 Header: Displays Title

 Detail: Displays fields
 Footer: Displays additional information

87 Copyright Crespo Technology Services Group, Inc.

 Form Creation Tools
Form Creation Tool Description
Form Creates a form with all fields in a table
Form Design Helps you create a form in Design view
Split Form Lets you view the form in Form view and Datasheet view
simultaneously
Multiple Items Lets you view multiple records from the table in the form of a
spreadsheet
PivotChart Helps you create a form in PivotChart view
Blank Form Use a blank form to build a form from scratch
Form Wizard A tool that helps you through the process of creating a form

88 Copyright Crespo Technology Services Group, Inc.

 Modifying the Design of a Form
 Can be dome by modifying the TAB order
 Add, delete or modify controls
 Bound: Associated to a field in the table or query
 Unbound: Not associated with data
 Calculated: Displays data obtained by evaluating an expression

89 Copyright Crespo Technology Services Group, Inc.

 Generating Reports

90 Copyright Crespo Technology Services Group, Inc.

 Report Views
View Description
Design Ability to add, modify or delete controls. Cannot view data.
Report View data without ability to make changes to controls
Layout View data and ability to make changes to controls
Print Preview Check how the report will print

91 Copyright Crespo Technology Services Group, Inc.

 Report Sections

 Header: Displays Title

 Detail: Displays fields
 Footer: Displays additional information

92 Copyright Crespo Technology Services Group, Inc.

 Report Creation Tool
View Description
Report Creates a report with all the fields
Blank Report Create a blank report from scratch
Report Design Helps you create a report in Design view
Report Wizard Helps create a report by following step-by-step instructions

93 Copyright Crespo Technology Services Group, Inc.

 Report Creation Wizard

94 Copyright Crespo Technology Services Group, Inc.

 Report Printing

95 Copyright Crespo Technology Services Group, Inc.