PgP MIS 202 Access Overview 1
Microsoft Access
Introduction to Relational Databases
Powerful tool to collect and analyze business data, facilitates decision-
making
PgP MIS 202 Access Overview 1-2
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-3
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-4
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-5
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-6
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-7
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-8
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 9
Microsoft Access
Database and Table Design
Requires up front planning to create useful database
PgP MIS 202 Access Overview 1-10
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-11
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-12
Design Aids
Database and Table Wizards Learn typical field settings and properties by
observing how Microsoft does it
PgP MIS 202 Access Overview 1-13
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-14
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-15
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-16
Table Modifications-Design
Be aware of ramifications Possible Field actions:
Delete Move Add Modify Properties
PgP MIS 202 Access Overview 1-17
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-18
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 19
Microsoft Access
Query Basics
Obtaining Answers to Data Questions
Creating a query is easy, creating a query that does what the client or your
co-worker wants is difficult.
PgP MIS 202 Access Overview 1-20
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-21
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-22
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-23
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-24
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-25
Querying Databases
Specifying Record Selection Criteria-learning to return just the records you need
Learn to formulate expressions, used in: Query criteria and calculated fields
PgP MIS 202 Access Overview 1-26
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-27
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-28
Querying Databases
Aggregate Functions-useful for gathering statistical information, watch “group by”
PgP MIS 202 Access Overview 1-29
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