Busn 216
MS Access
1) Tables
1) The heart of any database
i)
Every database
must have at least one table
2) Tables are were we store the raw data
3) First we create the tables, and then from tables we
can make:
i)
Forms
ii)
Queries
iii) Reports
4) Tables contain:
i)
Fields (Columns)
(1)
Basic fact or
data element
(2)
Store data in
smallest parts
(a)
Example: For
addresses, separate City, State, Zip
(3)
Reduce
redundancy
(a)
Example:
(i) Enter data element only once, into only one table
(ii) Don’t use calculated fields
ii)
Records (Rows)
(1)
Set of fields
iii) Tables are sets of records
2) Elements of Table:
1) Primary Key
i)
Set in design
view and helps to prevent duplicate records (helps to assure that each record
is unique)
2) Status Bar (bottom left)
i)
Record indicator
ii)
Navigation Bar
3) Record Selector ►
i)
Indicates record
has been saved
4) Pencil in row header …!
i)
Indicates record
has not been saved
5) Move through fields & records with any of the
following keys:
i)
Tab
ii)
Arrow
iii) Enter
iv) If you get stuck use: “Esc” key
6) Find:
i)
Ctrl + F
7) Edit
i)
Highlight &
modify
8) Add Record
i)
Insert, New
Record
ii)
New Record
button
iii) Ctrl + +
9) Delete
i)
Select record
& delete
ii)
Right click row
& delete
3) Elements of Table in Design View:
1) Field name
i)
Up to 64
characters
ii)
Cannot use: .,
[, ] ,!, `
iii) Can not use same name for different fields
2) Primary Key (with field selected, click button on
toolbar)
i)
Helps to prevent
duplicate records
(1)
Helps to assure
that each record is unique
3) Data Type (Data Validation)
i)
Number field
(1)
Numbers, decimal
points, +/- sign
(2)
Can be used in a
calculation
ii)
Text field
(1)
Letters,
numbers, special characters
(2)
Up to 255
characters
(3)
Numbers that are
not used in calculations
iii) Memo field
(1)
Descriptive
(2)
Up to 64,000
characters
iv) Date/Time field
(1)
Formatted
dates/time
(2)
Can be used in
date/time calculations
v)
Currency field
(1)
Monetary values
(2)
Can be used in
calculations
vi) Logical field (Yes/No field)
(1)
Yes or No
vii) OLE (object linking and embedding) Object Field
(1)
Contains an
object created by another application
viii)
AutoNumber field
(1)
Each time you
add a record, Access assigns the next consecutive number
(2)
AutoNumber field
is unique for each record (good to use for Primary key)
ix) Hyperlink field
(1)
Stores Web
address
4) Description
i)
Description
should help a person to understand the purpose of the field
5) Properties of the fields:
i)
Field Size
(1)
Adjusts the size
of text
(2)
Limits numerical
values
ii)
Format
(1)
Example:
(a)
> Makes
lowercase letters appear as uppercase letters
(b)
< Makes
uppercase letters appear as lowercase letters
iii) InputMask
(1)
Displays
formatting prior to data entry
(a)
Example:
automatic telephone number formatting
(i) Click button with three little dots
(ii) Click yes to save table
(iii) Highlight phone input mask
(iv) Click finish
(2)
Code that will require
letters as opposed to numbers:
LL
(3)
Search Help for
more information: Type “InputMask Property”
iv) Caption
(1)
Specifies label
other than field name for reports and forms
v)
Default Value
properties
(1)
Default value
for each record that is added to the table
vi) Validation Rule property
(1)
Rejects record
that does not conform to rule
(2)
Use “Or” &
“And”
(3)
Type “words”
that are acceptable
vii) Validation Text properties
(1)
Specifies error
message when validation rule is violated
viii)
Required
properties
(1)
Rejects record
that does not have a value entered for this field
ix) Allow Zero Length properties
(1)
Allows strings
of zero length
x)
Indexed
properties
(1)
Finding &
sorting records is faster and more efficient
xi) Unicode Compression properties
(1)
Set to yes to
help store data efficiently
xii) IME mode and IME Sentence Mode properties
(1) Input Method Editor for Asian languages
4) Designing a Table:
1) Include all the necessary data
i)
What sort of
information will be expected from the system?
2) Store data in its smallest parts (Allows flexibility)
i)
Is it better to
have one field for the address, or is it better to have one field for each of
the following: address, city, state, and zip?
3) Do not create calculated fields
i)
Define:
calculated fields
(1)
Fields that are
composed of other fields that already exist elsewhere in the database (this
wastes space: “redundancy”
5) Records within a Table or Form:
i)
Click in field
in which you would like to search
or
ii)
Ctrl + F
(1)
Find and Replace
dialog box appears
(a)
Find What:
(i) Type what you want to find
(b)
Look In:
(i) Automatically filled in
(c)
Match:
(i) Whole field (Exact match)
(ii) Any part of Field
i)
Highlight and
modify
3) Add
new record
i)
Any of the
following methods:
(1)
Insert, New
Record
(2)
New Record
button
(3)
Ctrl + “+”
4)
Delete
record
i)
Delete Record in
Toolbar
(1)
Displays a subset
of records according to specified criteria
(1)
Click in cell
with desired criterion
(2)
Right-click, Filter
by Selection
(3)
Status bar
indicates:
(a)
That object has
been filtered
(b)
The number of
records in the object
(4)
Perform multiple
filters
(5)
Remove Filter
button
(a)
Removes all
filters
(6)
Filter Excluding
Selection
(a)
Right-Click, Filter
Excluding Selection
(1)
Click Filter by
Form button
(2)
Use drop down
arrows for each field to specify criterion to filter data
(a)
Add criteria to
one or more fields
(i) Relational operators
(ii) OR (Or tab at the bottom of the window)
(3)
Click Apply
Filter button
(4)
Click Close
button to close
(1)
Record, Filter,
Advanced Filter/Sort
(a)
Clear Sort and
Criteria rows
(b)
Click Apply
Filter button
(2)
If all filters
are deleted, Apply Filter button should be dimmed
(1)
List records
alphabetically or numerically
(1)
Click in the
field on which you want to sequence the records
(2)
Click the Sort
Ascending or Sort Descending buttons
(1)
Point to field
heading with black-arrow
(2)
Click and drag
to select two field columns
(3)
Click Sort
button
(1)
Click field
heading
(2)
Point to field
heading with white-arrow curser
(3)
Click and drag
field to new location
v) Major
Keys to left, Minor Keys to right
(1)
Use Sort and
Filter together (in any order) to create any desired sequence
6) Relational Database
1)
Information that is in multiple tables is
stored in only one table
i)
Modifications are made in only one place
ii) Saves
time and computer space
iii) Similar
to assumption tables in Excel
2) Define
Terms:
i)
Primary Key:
(1)
Unique Identifier
ii)
Foreign Key
(1)
A field in one table whose values must match
the primary key of another table
iii)
Referential Integrity
(1)
When creating a relationship between tables, referential
integrity insures that only a proper entry is made into the field:
(2)
The entry into the foreign key must be in the
primary key of the other table.
(3)
Automatically creates data validation that
requires that you enter the proper field into the superordinate table
(a)
You can only enter a value that is already in
the subordinate table
(4)
Disallows deletion of fields in subordinate
tables if field is used in superordinate table
iv) One-To-Many-Relationship
(1)
One record in the subordinate table matches
many records in the superordinate table
v) Cascade
Update Related Fields
(1)
When you change a primary record in the
subordinate table, the “many” records in the superordinate table change also
vi) Cascade
Delete Related Records
(1)
When you delete a primary record in the
subordinate table, the “many” records in the superordinate are deleted also.
3) View
Subdatasheets
i)
Subordinate tables
(1)
Show details from superordinate tables
(i) Click
“+” sign to show details specified record
1. Add
or delete records as necessary
4) Relationship
Window
i)
After tables are created, you must create the
connections between the tables
(1)
Tools, Relationships
(2)
Relationships, Show table
(3)
Double click to add tables to Relationship
window
(4)
Click and drag fields to connect Primary keys
to Foreign keys
(i) Edit
Relationships dialog box appears
(ii) Check
the box to Enforce Referential Integrity
(iii) Click
create
(iv) “1
– ∞” symbolizes the one-to-many relationship
7) Indexes
1) Makes
internal procedures for finding records faster and more efficient
2) Makes
internal procedures for sorting records faster and more efficient
3) Without
index, Access will still sort and find, but it will do it more slowly
4) The
efficiency gained and the time saved is not obvious in small databases (<
50). In large databases the time saved can be dramatic
5) Good
Example on page 3.44 (Zip Codes)
6) Access
indexes primary keys and zip codes automatically:
7) After
you request Access to create index:
i)
Access sets it up automatically
ii) Maintains
index automatically
iii) Automatically
uses index when it is efficient to do so
(1)
Example request Access to sort
8) Indexes:
i)
Advantages:
(1)
Find records more quickly
(2)
Sort records more quickly
ii) Disadvantages:
(1)
Take up space on disc
(2)
Access must take time to maintain indexes
9) When
should you use Indexes?
i)
When you will frequently use find
ii) When
you frequently sort
iii) When
you use a foreign key
iv) (Primary
keys automatically use indexes)
10) Create
Index:
i)
Single-field index
(1)
With field selected in Design View of Table,
select Index in Property Pane (specify whether you want duplicates)
ii) Multiple-field
index
(1)
Click Index button on Table Design toolbar
(i) Index
Name:
1. Enter
Name
(ii) Field
Name:
1. Enter
fields
(iii) Sort
Order: Descending or Ascending