pgp mis 202 access overview 1 microsoft access introduction to relational databases powerful tool to...

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


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


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


Database Objects-Queries

Select queries answer questions using data stored in a database

Action queries change database data Query Wizard enables learning query

creation


Database Objects-Forms

Allows you to maintain and view records in a database

Mainly for on-screen output Form Wizard enables learning form creation


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


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…


Microsoft Access

Database and Table Design

Requires up front planning to create useful database


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


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


Design Aids

Database and Table Wizards Learn typical field settings and properties by

observing how Microsoft does it


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


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


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


Table Modifications-Design

Be aware of ramifications Possible Field actions:

Delete Move Add Modify Properties


Table Modifications-Records

Possible actions: Copy(are field structures similar?) Open multiple copies of Access? Delete Modify Copy field value from previous record, Ctrl+’


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


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.


Querying Databases

Filters-datasheet tools to temporarily display set of records Filter by Selection Filter by Form Advanced Filter


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


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?


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


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


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


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


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)


Querying Databases

Aggregate Functions-useful for gathering statistical information, watch “group by”


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 microsoft access introduction to relational databases powerful tool to...

Documents

field valuespgp mis

rollbackpgp mis

tablepgp mis

itpgp mis

formatpgp mis

data redundancypgp mis

field data typesdecide

table design viewchoose