Transcript Photoshop: Creative Uses

Intermediate MS Access
Instructor: Vicki Weidler
Assistant: Adam Cavotta
Unit 9: Creating Relational
Databases
Overview
•
•
•
•
•
Normalize tables to reduce data
redundancy
Understand the requirements for first,
second, and third normal forms
Set one-to-one, one-to-many, and
many-to-many relationships between
tables
Implement referential integrity
between related tables
Use cascading deletes and updates to
prevent orphan records
Normalization
The process of restructuring data files
to:
• reduce data to its simplest structure
• minimize redundancy
• achieve the most efficient and functional
yet flexible way to store data
First Normal Form (1NF)
Eliminates duplicate data…
• a table should not contain similar information in several
fields; eliminate duplicative columns from the same
table or move repeating fields to a related table
• a table should not contain fields that can be further
broken down into smaller meaningful parts
• create separate tables for each group or related data
and identify each row with a unique column (the
primary key); each table should describe a single entity
(object) class
Definition
1NF
Second Normal Form (2NF)
Requires all data in the table apply directly to the subject of the
table indicated by the primary key field…
• if the table has a single-field primary key and meets the
requirements for 1NF, then it also meets the requirements for
2NF
• however, if the table has a composite (multiple-field) primary
key, then all the fields in the table must be related to and/or
dependent on ALL the fields of the composite primary key
• to convert a table to 2NF, find all the fields that are related to
only part of the composite (multiple-field) primary key, group
these fields into another table, then assign a primary key to
the new table
• another way of looking at it is to remove ALL subsets of
duplicate/redundant data that apply to multiple rows of a table
and place them in new tables, then create relationships
between these new tables and the original tables by using
foreign keys
Definition
2NF
Third Normal Form
(3NF)
Eliminates fields that can be derived from
other fields…
• if a table contains fields that do not
relate to and are not fully dependent on
the primary key and completely
describe the object that table
represents, then it is not in 3NF
• to convert a table to 3NF, delete all the
fields that do not relate to the primary
key
Definition
3NF
Fourth Normal Form (4NF)
Requires that tables not contain fields for
two or more independent, multi-varied
facts…
• this rule requires splitting tables that
consist of lists of independent attributes
• i.e. in a relation table, job skills and
language fluency are independent facts
about an employee; therefore, the 3-field
relation table must be split into 2 twofield relation tables
1
2
3
4
French
German
Russian
Spanish
1
2
3
4
John
Jane
Bob
Elaine
1
Project Mgmt
2
Supervisory
3
4
Software
Organization
Doe
Smith
Healy
Anderson
Employee
Code
1
Language
Employee
Code
Job Skill
2
1
3
1
3
2
3
2
1
3
4
4
4
4
1
Fifth Normal Form (5NF)
Requires the capability to reconstruct the source
data from the tables that have met 1NF, 2NF, 3NF
and 4NF…
• you should be able to regenerate from the
database a spreadsheet that contains all the
data in the original version
• this is an “acid test” for normalization and
requires considerable skill in designing queries
to join related tables to produce a readable
output to compare with the original data
Relating Tables
Relationships
used to extract data from
several tables at the same time
Primary key
unique identifier for each
record in a table
Foreign key
the primary key in the related
table
Types of Relationships
One-to-One Relationship
When one complete record in the primary table is related to
just one record in the related table and vice versa; both tables
are equally dependent on each other
One-to-Many Relationship
When one record in the primary table is related to several
records in the related table; however, a record in the related
table has only one related record in the primary table
Many-to-Many Relationship
When several records in one table are related to several
records in another table. A many-to-many relationship cannot
be created directly in MS Access. To relate such tables, you
must first create a junction table that contains the primary
keys of both tables
Referential Integrity
Referential Integrity
Ensures that changes made to one table will be
reflected in the related table
Orphan Records
When a record is deleted in the primary table
without deleting the related records in the
other table
Referential Integrity Cont.
Cascading Deletes
When you delete a record from the primary
table, cascading deletes automatically deletes
all related records from other related tables;
this ensures that there are no orphan records
Cascading Updates
When you change a primary key in a table, the
change is updated in all of the related tables
Summary
•
•
•
•
•
Normalize tables to reduce data
redundancy
Understand the requirements for first,
second, and third normal forms
Set one-to-one, one-to-many, and
many-to-many relationships between
tables
Implement referential integrity
between related tables
Use cascading deletes and updates to
prevent orphan records
Independent Practice
Activity
Unit 10: Working with
Related Tables
Overview
• Create Lookup lists using the
Lookup Wizard & Design View
• Modify Lookup field properties
• Learn how to change control
types
• Add data to related tables
utilizing a subdatasheet
Lookup Wizard
The Lookup Wizard creates a field
in one table that gets data from a
field in another table. This is
called a Lookup list field.
The Lookup field uses the primary
key from the related table to
ensure referential integrity.
Table Control Types
Text Box
used to enter data in a table; cell
Combo Box
drop-down list of values; you can
either select a value from the
or enter a value not on
list
the list
List Box
select a
cannot
on the list
drop-down list of values; unlike the
combo box, you can only
value from the list, you
enter a value that is not
Adding Data to Related Tables
Subdatasheet
• Set of records extracted from a
table based on a record in another
table
• Use to view and enter data in
related tables
Summary
• Create Lookup lists using the
Lookup Wizard & Design View
• Modify Lookup field properties
• Learn how to change control
types
• Add data to related tables
utilizing a subdatasheet
Independent Practice
Activity
Unit 11: Defining Data
Entry Rules
Overview
• Create field input masks using
the Input Mask Wizard
• Set Required, Allow Zero Length,
and Field Size properties
• Define validation rules and text
for entering data in a field
Input Masks
Input masks allow you to control the
way data is entered in a field
• First section contains the input mask
• Second section determines whether to store
literal characters with the values that are
entered
• Third section specifies placeholder
characters
Input Mask Characters
Character
Description
0
Must enter a number between 0 and 9
9
Can enter a number or space (0 through 9), but entry is
not required
#
Can enter a number or a space; entry is not required
and all blank positions will be converted to spaces
L
Must enter a letter, A through Z
?
Can enter a letter, A through Z, but entry is not required
A
Must enter either a letter or a number
a
Can enter a letter or a number, but entry is not required
&
Must enter a character or a space
C
Can enter a character or space, but entry is not
required
<
Any letter that follows will be converted to lowercase
>
Any letter that follows will be converted to uppercase
Setting Properties
Property
Required
AllowZeroLength
Field Size
Description
Used to specify that a field is
required and a value must be
entered in it
Used to specify that the field
can contain null values
Used to specify the maximum
number of characters that
can be entered in the field
Validation Rules
You can set conditions on types of data,
data format, or number of characters
that can be entered in a field.
If the data entered in the field violates
the validation rule, Access
automatically prompts for a correct
value.
Setting Validation Rules
Rule
Description
Like “S???”
The value in the field should
have 4 characters and the
first character should always
be an S
<>0
The value in the field should
not be equal to zero
The value in the field should
be 0 or greater than 100
0 or >100
<#1/1/2000
The date in the field should
be earlier than January 1,
2000
Validation Text
When a validation rule is violated, you
can display your own text in the
warning message box.
Summary
• Create field input masks using
the Input Mask Wizard
• Set Required, Allow Zero Length,
and Field Size properties
• Define validation rules and text
for entering data in a field
Independent Practice
Activity
Unit 12: Using Advanced
Query Features
Overview
•
•
•
•
•
•
Understand the differences between
outer, inner, and self-join queries
Design outer, inner, and self-join
queries
Generate calculated fields in a query
Modify the format of a displayed value
Use the expression builder
Create queries to add, delete, and
modify data in a table and to produce
new tables
Joins
Queries that include data from more
than one table are called joins. A join
tells MS Access how the data between
tables is related. You can create:
• Outer joins
• Inner joins
• Self-joins
Outer Joins
An outer join is a query in
which all of the records from
one table are joined to only
matching records from another
table.
Inner Joins
If one table does not have a matching record
in the second table, then no records from
either table will appear in the query results.
To avoid this, you can use an intermediate
table to extract data from the original two
tables. This intermediate, or join table, acts
as a bridge between two tables that don’t have
any related fields. This is used when a manyto-many relationship exists between two
tables and uses primary keys from both tables
to extract the data from the intermediate join
table.
Self-Joins
A self-join is a query that
displays matching records
from the same table when
there are matching values in
two fields.
The Expression Builder
Action Queries
Action queries perform certain actions
in tables. There are four types:
• Append-append data from one table to
another
• Delete-delete records from tables based on
specific criterion
• Update-update data in different tables at the
same time
• Make-Table-creates a table from the result
of a query
Summary
•
•
•
•
•
•
Understand the differences between
outer, inner, and self-join queries
Design outer, inner, and self-join
queries
Generate calculated fields in a query
Modify the format of a displayed value
Use the expression builder
Create queries to add, delete, and
modify data in a table and to produce
new tables
Independent Practice
Activity
Unit 13: Creating
Advanced Queries
Overview
•
•
•
•
•
•
•
Use a select query to summarize and group data from
different tables
Write a concatenation statement to combine values
from different fields
Use the Find Unmatched Query Wizard to locate
records that do not match between tables
Design a crosstab query to summarze grouped data
Create single- and multiple-criteria parameter queries
Learn how to use a wildcard in a parameter query
Create single- and multiple-field indexes to quickly
sort and filter data in a table
Concatenation
Concatenation is the
process of combining values
from different fields into one
field
Find Unmatched Queries
Use a Find Unmatched Query
to view records that don’t have
a matching record in another
table
Example: Locate customers
who don’t have matching
entries in the Orders table.
Crosstab Queries
Display a summary of values based on two
different types of information.
Generally used to display large amounts of
data in a compact format for easy review.
Used to calculate averages, sums, or other
totals on records, then group the results into
two types of data.
One group runs down the left side of the
datasheet while the other runs across the top.
Parameter Queries
A parameter query displays
results based on criteria you
specify each time you run
the query.
One or multiple parameters
can be used.
Indexes
Indexes are used to quickly
sort and find data in tables.
An index performs a search
based on key values.
Primary keys are
automatically indexed.
Summary
•
•
•
•
•
•
•
Use a select query to summarize and group data from
different tables
Write a concatenation statement to combine values
from different fields
Use the Find Unmatched Query Wizard to locate
records that do not match between tables
Design a crosstab query to summarze grouped data
Create single- and multiple-criteria parameter queries
Learn how to use a wildcard in a parameter query
Create single- and multiple-field indexes to quickly
sort and filter data in a table
Independent Practice
Activity
Unit 14: Creating
Advanced Form Design
Overview
•
•
•
•
•
•
•
•
Add a graphic control to a form
Embed a graphic in an unbound object frame
Bind a control to a calculated field
Add calculated fields to a form
Insert a combo box and modify its properties
Format controls in a form
Insert unbound controls
Change the control tab order
Controls
Unbound – not linked to any field in a
table or a query
Bound – linked to a field in a table or
query
Calculated – used to display a
calculated value based on one or more
fields in a table or query
Summary
•
•
•
•
•
•
•
•
Add a graphic control to a form
Embed a graphic in an unbound object frame
Bind a control to a calculated field
Add calculated fields to a form
Insert a combo box and modify its properties
Format controls in a form
Insert unbound controls
Change the control tab order
Independent Practice
Activity
Unit 15: Using Advanced
Report Features
Overview
•
•
•
•
•
•
Create customized headers and footers
Set properties to group data and modify a
report’s appearance
Use functions to add calculated values in a
report
Apply conditional formatting
Embed a subreport in a main report
View and troubleshoot report results
Report Headers & Footers
Header – first page of report &
includes logo, image, report
title
Footer – last page of report &
includes details such as report
totals
Conditional Formatting
Conditional formatting draws
attention to data in a report if
it meets specified criterion.
KeepTogether Property
The KeepTogether property
ensures that a complete
section of the report is always
printed on one page.
Group Footers
Group footers are used to
group a report based on a
given field.
Use to add information such as
group totals or group names
ForceNewPage Property
To print each set of related data on a
separate page, you can use the
ForceNewPage property.
Can be used in all sections EXCEPT the
page header & footer sections.
Can insert pages before a section, after
a section, or both.
HideDuplicates Property
To display only the unique
values in each field of a report,
you can use the
HideDuplicates property.
DateDiff Function
Used to calculate the difference between two
dates.
DateDiff (“interval”, [date1],[date2])
“Interval” refers to whether the calculation is
the difference between days (d), months (m) or
years (y).
Both fields must have Date/Time data types in
order for this function to work properly.
IIF Function
Used to evaluate a condition. If a condition is true, the
IIF function returns one value. If it is false, it returns a
different value.
IIF (condition, value_if_true, value_if_false) is the syntax
for the function.
Condition means the condition you want the function to
evaluate.
Value_if_true is the value to be returned if the condition
is true.
Value_if_false is the value to be returned if the
condition is not true.
Subreports
Used to display data from two
different reports.
A subreport is embedded in a main
report.
Generally, the main report and
subreport share a common field by
which they are linked.
Summary
•
•
•
•
•
•
Create customized headers and footers
Set properties to group data and modify a
report’s appearance
Use functions to add calculated values in a
report
Apply conditional formatting
Embed a subreport in a main report
View and troubleshoot report results
Independent Practice
Activity
Unit 16: Creating Charts &
Data Access Pages
Overview
• Create and modify a chart in a form
• Create and modify a chart in a
report
• Understand the difference between
record-based and global charts
• Create a data access page
Charts in MS Access
Record-Based – displays data
based on the active record &
changes each time you navigate to
a new record
Global – chart remains the same
no matter what record you display
Data Access Pages
Web pages that allow you to
view, add, edit, or delete data
in the source MS Access
database
You can sort, filter or group
data using data access pages
Summary
• Create and modify a chart in a form
• Create and modify a chart in a
report
• Understand the difference between
record-based and global charts
• Create a data access page
Independent Practice
Activity
Conclusion
• Resources
• Questions & Answers
• Evaluations
• Thank You!!!