Transcript Microsoft Access 2000

Microsoft Access 2000
Relational Database
Software
Note before we begin:
Converting a database
You CAN convert Access 95, 97
directly to 2000
 You CANNOT convert Access 2000
directly to 95 or 97
 Save it down > Tools/Database
Utilities/Convert Database/to Prior
Access Version

2
General Concepts
3
What is a Database and
what is it used for?
A database is an organized collection
of data related to a particular topic
or purpose.
 The primary function of a database
is to enable the user to organize and
retrieve information in a manner
defined by the user.

4
Access




Relational database software with a
graphical user interface.
Access integrates easily with Excel
& Word.
Easy to get started with pre-made
tables, forms, queries & reports.
Can use to create database
applications by incorporating
macros & modules (Visual Basic).
5
Flat-File vs. Relational


A Flat-file database consists of a
single database file or table
which contains all the
information about a topic. It
does not physically link or point
to other files.
A Relational database consists of
multiple tables linked together
by at least one common field.
6
STUDENT INFORMATION TABLE
Name
Mary Wills
Address
ClassID ClassName Instructor
Time
1010 1st St. CO150 Composition Matthews
3 MWF
Jim Johnson 234 Maple CO150 Composition Matthews
3 MWF
Mark Smith
3 MWF
111 W. 3rd CO150 Composition Matthews
Products Table
ProdID
ProductName UnitPrice Quantity SupplierID
1234
Cajun Seasoning $1.00
2000
S450
Supplier Table
SupplierID SupplierName Address Contact
S450
New Orleans
Delights
New
Orleans,
LA
Phone
Anne Rice 800-555-1111
The Database window
Menus and toolbars
 Command Center – all operations
start here
 Categories of objects on left
 Objects and creation icons on right
 Status bar

9
Access Database Objects
Tables
 Forms
 Reports
 Queries
 Macros
 Modules
 Pages

10
Tables




Tables are the heart of Access.
All data stored in tables. Fields (columns)
and records (rows) intersect to form cells.
Each table should contain information
about one subject only.
Each field contains a specific type of
information such as text, number,
currency, dates, etc.
11
Tables (cont.)



Two or more tables are linked
together through fields they have in
common. This is done by defining
relationships.
Access creates relationships between
different tables, about different but
related subjects
A table should contain data about 1
topic only, e.g., not students and
faculty in one.
12
Queries





Create a subset (dynaset) of data.
Means for obtaining data from 1 or
more related tables.
Used to sort and/or select records
according to your criteria.
Used as the basis for reports.
Action queries can be used to
update, archive, delete, append
records, etc.
13
Forms



Used to view, edit, and enter data. You
can also enter data directly into a table.
They can be customized for convenience.
Best for on-screen viewing.
Forms may be designed to simplify data
entry and data editing.
Can use controls to make data entry
easier and more consistent such as dropdown lists, radio buttons, etc.
14
Reports



Sorted and summarized data. They can
be designed to show only the data you
want to show. Best for printed materials.
Used for printing data in an organized,
professional looking manner.
Can add subtotals, groupings, etc. to
reports.
15
Macros

Automatically carry out one or more
tasks. Best for tasks that you
perform often.
16
Modules

A set of procedures stored as a unit
to perform an action. Written in
Visual Basic.
17
Pages

A data access page for viewing on
the Internet, or an intranet.
18
Table Design vs. Table
datasheet view



Toggle in the upper right corner. Flips
between the data itself (table) and the
underlying structure (design) of the data.
Datasheet allows you to enter data, sort
and filter data, hide and freeze columns,
etc.
Design allows you to create tables, add
fields, and set and modify field
properties.
19
Navigating the datasheet




Navigation buttons in lower right hand
corner – one record at a time, beginning,
end, new record, or type the number in.
PgUp or PgDn for page at a time
CTRL + Home for beginning, CTRL + End
for end
F5 moves you to record box, type record
number, enter
20
Resizing rows and columns
Similar to Excel
 Click and drag border
 D-click right border for autofit

21
Sorting




Automatically sorted by primary key
(unique identifier assigned to every table)
Select sort column, hit ascending or
descending sort button
Can select multiple adjacent columns –
will sort left to right
Very simple – filters and queries for more
flexibility
22
Selecting data in datasheet
mode








Part of cell - Click and drag
Entire cell – Click left edge (+ mark)
Adjacent fields – Click left edge and drag
Column – Click top of column (black arrow)
Row – Click side of row (black arrow)
Multiple columns – Click and drag black arrow
Multiple rows – Click and drag black arrow
All – CTRL + A or upper left box
23
Rearranging, hiding,
freezing








To move a column, select, then drag
To hide a column, select it, Format/Hide
For confidential information
Format/Unhide to restore
To freeze a column, select it,
Format/Freeze
Will go to left side
To keep it in view as you scroll
Format/Unfreeze to restore
24
Printing datasheets
Hide and/or size columns for best
view
 Change formats, fill colors
 File/Page setup for orientation and
columns
 Landscape is often best orientation
 Preview before printing

25
Manipulating data
26
Adding records






►* for new record
Always add to bottom – Access will resort
when table is closed
No need to save – when you leave record,
Access will save
Records/Data entry for data entry mode
Current record only – less distracting
To remove – Records/Remove Filter
27
Editing records
Click inside field (pencil will appear
on left)
 Saves when you leave row or close
table
 Can only “Undo” last record, so BE
CAREFUL
 Within same record – hit escape to
Undo

28
Deleting data
Within a single field, select and hit
Del
 Select records or multiple records
from left, hit Del
 It will warn you once – no Undo, no
exiting without saving changes
 Large groups of deletes – use delete
query (Voter records purge)

29
Copying, moving data






Edit/Copy, Edit/Cut, Edit/Paste
CTRL + C, CTRL + X, CTRL + V
Use toolbar buttons
You must select the same number of
fields to paste into that you copied from
CTRL + ; - current date (doesn’t always
work)
CTRL + ‘ - copies data immediately above
30
Finding data
Edit/Find
 Choice of column or table
 Choice of all or part of text
 Shift + F4 for future searches

31
Filtering Records
32
Filtering Data

By Selection



By Form


Simplest; cannot sort at the same time
Can also filter “excluding” selection
Can use ‘and’ ‘or’ statements; cannot sort
at the same time
Advanced Filter

Use a filtering grid to enter expressions and
sort criteria; most like query grid; can sort
simultaneously, offers most flexibility.
33
Filter by selection
Select data you wish to look for
 Hit Filter by selection button
 All records that match will appear
 Multi-filter, by adding criteria
 Remove filter button when finished

34
Filter by form
Best for simple multi-criteria
 Data entry form will appear
 Type in criteria, hit filter by form
button
 Remove filter button when finished

35
Advanced Filter/Sort
Records/Filter/Advancrd filter-Sort
 Most versatile of three
 Can sort and filter in one step
 Only kind that can be saved (as a
query)
 File/Save as Query

36
Advanced Filter/Sort
Operators
>
 >=
<
 <=
 <>
*

greater than
greater than or equal to
less than
less than or equal to
not equal to
wildcard
37
Advanced Filter/Sort
Operators (con’t)
Not - eliminates criteria from
evaluation
 Between – finds criteria within a
range
 Like – used with wildcard (*)
 Null – no value

38
Examples of Advanced
Filter/Sort Operators







=CA - value is equal to “CA”
>=T - value begin with letter T, through Z
Is not Null - there is some value
<>CA, Not CA - All values but “CA”
>=1/1/91 - Date is on or after 1/1/91
Like G* - Values that start with G
Not like *oak* - All words that don’t have
“oak” in them
39
Advanced Filter/Sort criteria
Records/Filter/Advanced Filter-Sort
 Drag or D-click or pulldown fields
that are involved
 “Specify sort (ascending,
descending, neither)
 Specify criteria using operators,
text, numbers
 Hit Apply Filter

40
Multiple criteria – “and” vs.
“or”





If you are looking for an “and”
relationship, place both criteria on same
line
Vertical lines between boxes are “and”
relationships
If you are looking for an “or” relationship,
place criteria on different lines
Horizontal lines below “criteria” are “or”
relationships
“Or” statements can be typed together in
same box (R-click for “zoom”)
41
Practice session
“X” to clear grid
 A list of orders sent to Switzerland
sorted by shipping date (18 records)
 A list of orders shipped outside the
USA in 1996 (123 records)
 A list of orders with freight $50 or
over to Germany or Brazil sorted by
amount (90 records)

42
Database Design
43
Figuring out what you need



Determine the purpose of the database.
The subjects that need to be included and
the facts you need about each subject.
Determine the tables you need. Divide
information into separate subjects, and
have a different table for each subject.
Determine the fields you need. Decide
what information will be stored in each
table. Break the information down to its
smallest logical parts
44
Figuring out what you need
(con’t)


Determine which field will be the unique
identifier (key field). If there isn’t one
(i.e. – SS#), Access will supply one.
Names are not unique, and addresses
and phone numbers can change.
Determine the relationships. Decide how
the tables relate to each other. Add fields
and/or tables to help clarify these
relationships.
45
Sample database design




You have a list of workshops, people who
came, people who presented, how much
they spent
You want expenses by person, by
workshop, by category, various sub-totals
You want lists of attendees by workshop,
by location, by interests
You want to create mailing lists for future
workshops, based on interests and/or
location
46
4 interrelated tables
Table
 Table
 Table
 Table

of
of
of
of
workshops
attendees
presenters
presenter expenses
47
Workshop index










W# - key field
Name of workshop (subject/city)
Facility
Address 1
Address 2
City
State
Zip
Date of workshop
General Subject of workshop
48
Attendee listings











A# - key field
W# - (pulldown menu of subject/city)
Prefix
FName
LName
Phone number
Address 1
Address 2
City
State
Zip
49
Presenter listings











P# - key field
Prefix
FName
LName
Phone number
Address 1
Address 2
City
State
Zip
Yes/no field for each workshop subject
50
Presenter expenses









E# - key field
W# (pulldown menu of subject/city)
P# (pulldown menu of presenters)
Date
Travel expenses
Food expenses
Lodging expenses
Materials expenses
Misc. expenses
51
Creating the tables
Files/New/Database/Create to start
from scratch
 Table wizard will leads you through
decisions
 Design view toggles with datasheet
view

52
Setting the primary key







This is step 1
Default ordering by this field
Access won’t allow duplicates
Helps define and create relationships
between tables
Usually best to have Access assign one
(SS#, pre-existing employee # are
exceptions)
Hit the key icon
Set data type as “Autonumber”
53
Field properties – upper
grid




Field Name – no spaces – or other
applications can’t read it
Data type – number ONLY if you perform
math functions (Zip Code, phone
numbers best as text) – default is text
Lookup wizard to set up a pull down box
(pre-existing or new list)
Description – will show in status bar – let
others know what the field is for, how to
enter data, etc.
54
Field properties – lower grid
F6 toggles between upper and lower
 Each field created in upper grid has
a set of properties defined in lower
grid

55
Field size
Maximum number of characters an
entry could be
 50 is default for text field
 250 is maximum for a text field
 64,000 is the maximum for memo
field

56
Format



Displays numbers and text in a certain
format, usually numeric – dates,
currency.
Different formats displayed for different
data types (text has none listed)
Some text formatting: < is all lowercase,
> is all uppercase, @ will display “no
data” when nothing is entered
57
Input mask
Inserts characters that aren’t saved
in data, but help the imputer format
data – phone numbers, Social
Security numbers, dates
 Hit the “…“ button to see options

58
Caption
Specifies a label other than the field
name
 Spaces can be used here, as
opposed to the field name
 Default is field name

59
Default value
Automatically fills in a specific value
– current date is most common
 =Date() gives current date
 CO for home state
 Value can be changed after the fact

60
Validation rule
Limits data to certain restrictions
 Use Expression Builder (…) or just
type it in
 Use same standard operators from
advanced filters
 >=50
 =25 or=50 or=75

61
Validation text
Message displayed when data
breaks violation rules
 “Number must be greater than or
equal to 50”
 “Number must be in increments of
25”

62
Required, Allow Zero Length
These are similar
 “Required” requires that data be
entered
 “Allow Zero Length” applies to text
and memo only, allows a blank field
to be accepted

63
Indexed
Speeds searches on frequently
sorted or search fields
 Slows data entry, requires more
memory
 Only use when needed
 Key fields are always indexed

64
Relationships between
tables
65
Types of relationships

One to many – most common relationship
• Employee to Sales
• Each record in Sales matches only one record in
Employee, but one employee had many sales

Many to many – strongly discouraged,
unnecessary data redundancy
• Student to Classes
• Each record in one database matches multiple
records in another
• Solved by creating and intermediary table with oneto-many relationships at each end
• Pull the Key field from each table together in third
table is a simple way
66
Types of relationships
(con’t)

One to one – rare because it is two
dimensional, can use Excel instead
• Business information to personal
information
• Allows separation of databases, store
personal information in a limited access
format (password)
67
Viewing relationships






Tools/Relationships
Drag around boxes, size boxes, lines will
move
Common fields are linked by lines
Key fields are in bold
Key fields are often, not always, the
linking field
Infinity symbol means many, 1 means 1
68
Forming and editing
relationships
R-click on line to edit or delete
relationship
 Click and drag one field to same
field in different table to form
relationship
 One and many icons don’t show

69
Forming and editing
relationships (con’t)




Click on line to edit properties
“Enforce referential integrity” will turn
them on
“Cascade delete” will delete all records
with references to that record – BE
CAREFUL
“Cascade update” is not as dangerous –
will update all affected fields
70
Extra credit!

Can you find the table created to
link two many-to-many relationship
tables together?
71
Queries
72
Two kinds of queries

Select queries


Allow you to select, view, and analyze
data
Action queries
Allow you to modify data
 4 kinds: Update, Delete, Append, Make
table

73
Creating a query







Select the database
Queries/New
Design view
Select needed tables from the “show
tables” box
Close the box
Tables must be connected, or all
permutations will show, and your PC will
crash
Intermediary table can be used
74
Select the fields
Drag or D-click or pull down needed
fields to the field boxes
 To add all fields, click asterisk or Dclick the Table header

75
Specify sort order
Not required, but good to find an
easy way to look through the data
 Ascending or descending in selected
field
 More than one field selected, will
sort left to right
 You can sort with a hidden column
to circumvent this

76
Specify criteria
Limits results of the query
 Use same expressions as advanced
filter
 Multiple criteria use “and,” “or”
 Horizontal lines are “and”
 Vertical lines are “or”

77
Expressions and calculated
fields
Bring up the tables you are working
from (Queries/New/Design view)
 In Field grid, put field names in
brackets
 Join or calculate fields with
expressions
 R-click, choose build to build with
buttons

78
Expression and calculation
examples
[FirstName]+” “+[Last name]
 [City]+”, “+[State]+” “+[Zipcode]
 [UnitPrice]*[Quantity]
 [UnitPrice]*.25
 Expr1 is default column name, type
in your own to left of colon

79
Parameter Queries
For queries you run frequently, with
different values
 Design query normally, type prompt
text between brackets
 [Enter the last name], or [Enter the
date]
 When someone brings up the query,
it will prompt them for the variable

80
Save your query
Use save button, or File/Save
 Use default name or name it
yourself
 After save, it becomes part of the
database, and will show when you
hit the queries button in the
database window

81