Transcript Access1

Microsoft Access
Introduction to Relational Databases
Powerful tool to collect and analyze
business data, facilitates decisionmaking
PgP MIS 202 Access
Overview
1
Database Concepts-Databases
 Relational Database-collection of related
tables
 Common Field-connect records in separate
tables
 Primary Key-field(s) uniquely identify record
 Foreign Key-Primary Key from one table is
Common Field in another table
PgP MIS 202 Access Overview
1-2
Database Concepts-RDBMS
 Relational database management system functions







Create database structures
Add and edit records
Query capability (SQL)
Report generator
Data validation and protection
Data sharing
Ability to handle large amounts of data
PgP MIS 202 Access Overview
1-3
Database Concepts-Tables
 Field-single characteristic of a person, place,
object, event or idea
 Table-collection of fields that describe a
person, place, object, event or idea
 Field Value-specific field content
 Record-set of field values
PgP MIS 202 Access Overview
1-4
Database Objects-Queries
 Select queries answer questions using data
stored in a database
 Action queries change database data
 Query Wizard enables learning query
creation
PgP MIS 202 Access Overview
1-5
Database Objects-Forms
 Allows you to maintain and view records in a
database
 Mainly for on-screen output
 Form Wizard enables learning form creation
PgP MIS 202 Access Overview
1-6
Database Objects-Reports
 Allows you to view records in a database
 More formatting flexibility and options than
a form
 Mainly for printed output
 Report Wizard enables learning report
creation
PgP MIS 202 Access Overview
1-7
Database Administration
 Compacting-delete unused objects,
defragments the database
 Backup-protect from data loss or damage
 Restore-to make sure your backup scheme is
working
 Convert databases, link tables
 Many other topics-journaling, rollback…
PgP MIS 202 Access Overview
1-8
Microsoft Access
Database and Table Design
Requires up front planning to create
useful database
PgP MIS 202 Access
Overview
9
Designing Databases
 Determine fields needed to produce information to
make decisions (forms, reports)
 Group fields by subject/topic into tables
 Create primary key for tables
 Determine and set field properties
 Include common fields to link tables
 Normalize to avoid data redundancy
PgP MIS 202 Access Overview
1-10
Designing Tables
 Tables are composed of fields, set field
properties to accomplish your goal
 Develop and use a field naming convention
 Understand and set field data types
 Decide on field size, be aware of storage and
performance issues
PgP MIS 202 Access Overview
1-11
Design Aids
 Database and Table Wizards

Learn typical field settings and properties by
observing how Microsoft does it
PgP MIS 202 Access Overview
1-12
Table Design View
 Choose Field Name and Data Type

Description appears in status bar, good to add
 Choose Field Properties


Properties vary by Data Type
Common-Field Size, Required, Format
PgP MIS 202 Access Overview
1-13
Table Design View
 Choose Primary Key



Indexed: Yes(No Duplicates) unique
Indexed Fields speeds up searches but requires
CPU cycles
Entry required, no null values entity integrity
PgP MIS 202 Access Overview
1-14
Table Datasheet View
 Data Entry



Be aware of left column symbols, navigation
Data entry can also be done in forms
File>Save does not save record, that is done
when you move to another record
PgP MIS 202 Access Overview
1-15
Table Modifications-Design
 Be aware of ramifications
 Possible Field actions:




Delete
Move
Add
Modify Properties
PgP MIS 202 Access Overview
1-16
Table Modifications-Records
 Possible actions:





Copy(are field structures similar?)
Open multiple copies of Access?
Delete
Modify
Copy field value from previous record, Ctrl+’
PgP MIS 202 Access Overview
1-17
Table Relationships
 Defining and using table relationships is the
way to combine related data
 One to many

Unique record from one table can have zero, one
or many related records in another table
PgP MIS 202 Access Overview
1-18
Microsoft Access
Query Basics
Obtaining Answers to Data Questions
PgP MIS 202 Access
Overview
19
Querying Databases
 Filters-datasheet tools to temporarily display
set of records



Filter by Selection
Filter by Form
Advanced Filter
PgP MIS 202 Access Overview
1-20
Querying Databases
 Most common type- Select Query
 Asks a question about the data stored in
tables, only certain records meet the criteria
and are selected
PgP MIS 202 Access Overview
1-21
Querying Databases
 Query, Design View

Query By Example (QBE) is the Access user
interface that creates Structure Query Languate
(SQL) statements
 Query, Datasheet View


Display returned records, or ‘answers’
Can be different each time query is run, why?
PgP MIS 202 Access Overview
1-22
Querying Databases
 Creating queries is one of the most important
skills in any database program
 Queries are the basis for forms and reports
 Good form and report design is a result of
starting with a good query design
PgP MIS 202 Access Overview
1-23
Querying Databases
 Sorting, Ascending or Descending

Up to 10 fields, done from left to right
 Show check box- determines if field is
displayed in datasheet

Why needed? Set criteria for a field that you do not
want displayed
PgP MIS 202 Access Overview
1-24
Querying Databases
 Specifying Record Selection Criterialearning to return just the records you need
 Learn to formulate expressions, used in:

Query criteria and calculated fields
PgP MIS 202 Access Overview
1-25
Querying Databases
 Operators- key expression building tool


Like, In, Between…And…
=, <, >, <>
 Exact matches
 Multiple criteria


And-tends to return fewer records
Or-tends to return more records
PgP MIS 202 Access Overview
1-26
Querying Databases
 Calculated Fields-derivable quantities that
should not be stored (why?) in underlying
table, such as:


ExtendedPrice: Quantity * Price
Age: DateDiff("yyyy",[DateOfBirth],Date())
 Expression Builder-useful tool
 Zoom Box (Shift+F2)
PgP MIS 202 Access Overview
1-27
Querying Databases
 Aggregate Functions-useful for gathering
statistical information, watch “group by”
PgP MIS 202 Access Overview
1-28
Querying Databases
 Group By-use in conjunction with aggregate
functions or alone

Consolidate records into groups based on chosen
fields
 Useful in finding unique field values
PgP MIS 202 Access Overview
1-29