Transcript Chapter 2 Introduction to Microsoft Access

Introduction to Microsoft Access
Overview
1
Introduction
• What is Access?
• A relational database management system
• What is a Relational Database?
• Organized collection of data
• A relational database system creates separate containers
of logically related data, and stores each container in a
separate table.
• Tables are the key objects of a relational database
• Tables form the basis for information storage and
retrieval
• A collection of tables that are related to one another
form a database
• Related tables are linked via shared fields called keys
2
Relational Database Benefits
• Efficient storage of data
• One time data entry
• Improved Accuracy
• Good way to capture transaction data
• Sales data
• Enrollment data
• Order data etc.
3
Access Objects
• Objects are the structures you create and
methods you employ to store, manipulate
and retrieve data
• Objects include
•
•
•
•
•
tables
queries
forms
Reports
Macros
4
Tables
• Data is entered into Tables
• Backbone of a relational database
• Tables hold all stored data
• none of the other Access objects store data.
• Two dimensional
• rows (records)
• columns (fields/attributes of interest)
• Row ordering is unimportant,
• rows can be sorted and rearranged and not change the
fundamental table information
• Column ordering is unimportant,
• A table column may be placed in any particular
position
5
Each column(field) holds a different characteristic (attribute)
about the row (record) it describes
the primary key – Attribute(s) that makes each row(record) unique
6
Queries
• Ask questions
• (extract data for informational needs)
• Data stored in tables can be filtered and
sorted in queries
• Data filtered and sorted by query can be
displayed
• Data filtered and sorted by query can be printed
in reports
7
Query Types
• Selection (projection) queries.
• Select queries are the most common type.
• They pose questions of the database and return
answers in a dynaset(a virtual table)
• Subsets of rows(records) are returned when
selection criteria are specified to filter the data
• All attributes for each record are also returned
• Projection(a selection query) that returns a
subset of records and a subset of attributes for
each record when selection criteria are
specified to filter the data
8
Product Category table
9
Selection Query
Selection Criteria
10
Resulting Dynaset
11
Projection Query
12
Resulting Dynaset
13
Query Types
• Action Queries
• Alters(modifies) data in a table
• Add data records to existing table
from another table
• Delete records(fields) in a table
• Update records in a table
• Creates new table using data from an
existing table(s)
14
Forms
• Provide a way to view table data one row
at a time
• Easier data entry
• Easier data editing and modifications
• Easier validation checks on entered data
• Can display multiple records
• Display data from tables or queries but
do not actually hold data
15
Datasheet view of Customer Table
16
Form View for Data entry
17
Reports
• Used to preview and print data for
distribution
• Provide formatted soft copy(screen) output
• Provide hard-copy output
• Displays database information that can
be supplied by
• tables,
• queries, or
• both
• Can not be used to modify data
18
Example
report
19
Example report
20
Macros
• A macro is a tool that allows you to
automate tasks and add functionality to
• forms,
• reports, and
• controls.
21
Example macro
22
Example macro
23