Transcript Access

Using Microsoft Access
56:150 Information System Design
Introduction
In its simplest form, Access is a database
application
It uses a relational database management
model, which means you can relate each
piece of information to other pieces of
information by joining them.
Create Tables
By using wizard
By entering data
By design view
specify field name, data type
set general attributes
set primary key
Field data types
Text: Text or numbers that don’t require
calculations, such as phone number
Memo: Lengthy text
Number: Numerical data. Set the specific number
types in the field size property.
AutoNumber: A unique sequential (incremented
by 1) number or random number assigned by
Microsoft Access whenever a new record is added
to a table. AutoNumber fields can't be updated.
Date/time
Field data types
OLE Object: An object linked or
embedded in the table.
Yes/No
Look Up: This field allows you to choose a
value from another table or from a list of
values by using a listbox or combo box.
Currency
Hyperlink
Important Field Properties
Field Size: specify data type for numerical
data.
Validation rule: set constraint on field
Validation text: message when validation rule
isn’t satisfied
Default Value
Required
Indexed: To speed up the search and sorting
on the field. But may slow updates.
Establish Relationships
Click the relationships button on the toolbar
Tables are related by matching fields.
Enforce Referential Integrity
Referential integrity is ensure that relationships
between records in related tables are valid, and that you
don't accidentally delete or change related data.
Set Join type
inner join, left outer join, right outer join
Six types of Queries
Select query
Update query
Append query
Delete query
Make table query
Crosstab query
Create Queries
By using wizard
select table, fields
Not convenient to design complex query
By design view
By SQL view
Input SQL sentence directly
Design Reports
By using wizard
By design view
Example, Report “Invoice”