Transcript HRDC Access 1

MS Access: Database
Concepts
Instructor: Vicki Weidler
Overview
• Become familiar with MS Access database terminology
• Learn how to start MS Access, open a database &
navigate the software interface
• Understand database objects & their corresponding
functions
• Use standard design principles to plan a database
• Explore relationships between tables
• Use Help options to find information on MS Access
topics
• Learn how to close a database & MS Access
Databases
• What is a database?
• When do you use a database?
• 3 main types of databases
Flat file
Relational
Object-oriented
Microsoft Access
A relational database
management program that
allows you to store, organize,
and retrieve information in an
effective manner.
Planning a Database
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
What is the purpose of the database?
What do you need it to do?
Who will use it?
How many will use it?
When will they use it? (i.e. one at a time, simultaneously)
How will they use it? (i.e. desktop, server, web-interface)
Who will create it?
Who will maintain & modify it?
Who will do data entry?
Do you need to build in security measures?
Who will provide technical support for it?
What types of information need to go in it?
How will this be organized into tables?
How will these tables relate to one another?
What types of queries will you need to create?
Will you need to create any forms? How many? What kind?
Will you need to create any reports? How many? What kind?
Will you need to create any pages? How many? What kind?
Common Mistakes to Avoid
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Don’t assume every problem can be solved with a database
Don’t leave too long between reviews
Don’t spend too much time on the prototype
Don’t develop the database until you fully understand what you need it
to do
Don’t develop the database until it is clear where all responsibilities
lie
Don’t aid for 100% functionality first time around; use 80-20% rule
Involve all users in the database planning, design & testing
Has anyone else solved the problem? Don’t reinvent the wheel
Plan on an iterative development process
Use consistent database standards and naming conventions
Use consistent, universal coding
Use consistent viewing standards
Keep sufficient documentation; build into the database when possible
Tech support, tech support, tech support………..
Database Development Life-Cycle
Database
Design
Database
Production
Database
Testing
Database
Prototype
Database
Development
Summary
• Become familiar with MS Access database terminology
• Learn how to start MS Access, open a database &
navigate the software interface
• Understand database objects & their corresponding
functions
• Use standard design principles to plan a database
• Explore relationships between tables
• Use Help options to find information on MS Access
topics
• Learn how to close a database & MS Access
MS Access: Access Basics
Overview
•
Explore the viewing options for MS
Access tables, forms, queries &
reports
•
Learn how to navigate in a table and
a form
•
Learn how to run a query
Views in MS Access
•
Design View - displays the structure of a table, query, form or report
• Datasheet View – displays data in a tabular format containing rows &
columns in tables, queries & forms; view & edit
• PivotTable View – spreadsheet-like table used to analyze data
dynamically in different ways in tables, queries &
forms
• PivotChart View – presents data in a chart form in tables, queries &
forms
• SQL View – allows you to create queries using SQL statements
• Form View – allows you to view and edit data in a form
• Layout Preview – allows you to check a report’s design; shows
design elements & just enough records to verify the
design is correct
• Print Preview – allows you to view a report including moving around
a single page, multiple pages, look at several pages
at once & change magnification to view details
Using Queries to Retrieve Data
Define the following:
• The conditions that you want the data to meet
• The fields that you want to see in the query result
• The tables from which you’ll extract the fields
• The means of extracting data
Summary
•
Explore the viewing options for MS
Access tables, forms, queries &
reports
•
Learn how to navigate in a table and
a form
•
Learn how to run a query
MS Access: Creating
Databases
Overview
• Learn how to create & save a
database
• Use the Table Wizard to create a
table & set a primary key
• Use Design view to create tables
& enter records
Naming Rules
A database name or an object name:
• Can have any combination of letters, numbers, special
characters, & embedded spaces
• Cannot contain more than 64 characters & cannot start
with a space
• Cannot incluce a period (.), exclamation mark (!), an
accent grace (‘), or brackets ([ ])
CAUTION: Good practice to use underscores (_) instead
of embedded spaces because it is easier to refer to an
object name & avoid potential issues
Practice
•
•
•
•
•
•
•
•
•
•
Inventory_ _ Control
Yes, although one underscore is the accepted standard
.Access.Database 1
No, because the name contains periods & spaces
should be avoided
finanCIAL$Transaction
Yes, although try to use names that are as simple &
descriptive as possible
Contact [Client] Address
No, because the name contains brackets & spaces
should be avoided
SalesPerson3
Yes, it is fine to run words together & use different
cases
Summary
• Learn how to create & save a
database
• Use the Table Wizard to create a
table & set a primary key
• Use Design view to create tables
& enter records
MS Access: Working with
Fields & Records
Overview
• Learn how to modify a table’s design by
editing fields & setting field properties
• Learn how to add & delete records
• Use the Find feature to find & replace
records
• Use the Spell Checker to correct
mistakes in a table
• Learn how to sort & filter records
Effective Field Names
• Meaningful
• Descriptive
• Self-Explanatory
• Purposeful
• Understandable
• Readable
• Consistent
Text Formatting Characters
@
at least one letter or space must be
entered in the field
<
the letters entered in the field will be
converted to lowercase letters
>
the letters entered in the field will be
converted to uppercase letters
&
letters cannot be entered in the field
Sorting & Filtering
Sorting
Organize field data values in a
particular sequence (i.e. ascending,
descending)
Filtering
Temporarily isolate a subset of
records to analyze
Sorting Multiple Fields
• Fields must be adjacent
to each other in
Datasheet view
• Sorts from left to right
Summary
• Learn how to modify a table’s design by
editing fields & setting field properties
• Learn how to add & delete records
• Use the Find feature to find & replace
records
• Use the Spell Checker to correct
mistakes in a table
• Learn how to sort & filter records
MS Access: Querying
Tables
Overview
•
•
•
•
Learn how to create, run, print, and save queries
Use queries to sort data and filter query results
Learn how to modify query results
Understand how to modify queries by adding/
removing fields and by using comparison
operators
• Learn how to use AND and OR conditions
• Learn how to find records with empty fields
• Perform calculations in queries by using
expressions and aggregate functions
Comparison Operators
>
<
=
<=
>=
<>
Greater than
Less than
Equal to
Less than or equal to
Greater than or equal to
Not equal to
OR Conditions
Used to specify two conditions
in the criteria
Example:
Unit_price is greater than 2 OR
Qty_Available is equal to 700
AND Conditions
Used to show only the records
that satisfy all the specified
conditions
Example:
Unit_price is greater than 1.4
AND less than 1.9
Wildcard Operators
Used to locate text values
when you can’t remember the
exact text or word; placeholder
?
Used to substitute for a
single character
*
Used to substitute for any
number of characters
Empty Fields
•
Unknown (empty) values in fields are
referred to as null values
•
Null values cannot be used in
•
Can display or remove null values to
avoid/prevent problems
primary key fields or in calculations
Calculations in Queries
• When calculations are built into queries
(and not stored in the source tables) it
ensures the most up-to-date information
• Calculations are performed each time
the query is run
• Perform calculations on single records
or on a group of records
• Examples: Totals, Averages, Counts
Summary
•
•
•
•
Learn how to create, run, print, and save queries
Use queries to sort data and filter query results
Learn how to modify query results
Understand how to modify queries by adding/
removing fields and by using comparison
operators
• Learn how to use AND and OR conditions
• Learn how to find records with empty fields
• Perform calculations in queries by using
expressions and aggregate functions
MS Access: Creating &
Using Forms
Overview
•
Use the AutoForm feature to create
forms
•
Use the Form Wizard to create forms
•
Understand how to modify forms in
Design view
•
Use forms to find, sort, and filter
records
Summary
•
Use the AutoForm feature to create
forms
•
Use the Form Wizard to create forms
•
Understand how to modify forms in
Design view
•
Use forms to find, sort, and filter
records
MS Access: Creating &
Using Reports
Overview
•
Use the Report Wizard to create reports
•
Learn how to group records in a report
•
Learn how to summarize information in a
report
•
Understand how to change the report
layout
•
Learn how to print a report
Summary
•
Use the Report Wizard to create reports
•
Learn how to group records in a report
•
Learn how to summarize information in a
report
•
Understand how to change the report
layout
•
Learn how to print a report
Conclusion
• Resources
• Questions & Answers
• Evaluations
• Thank You!!!