Structure Elements
Download
Report
Transcript Structure Elements
IS 240
Access Presentation
One
1
Relational Database Programs
Data organized into tables
Columns called fields
Rows called records
Tables can be linked by a common
field
2
3
Advantages of Using a Database
Program
Ability to quickly locate specific
records
Search looks for a data match in a
specified field or fields
Same concept as a manual match, only
much faster
Records are easily added and
deleted
4
Advantages of Using a Database
Program
Easy record sorting by various
criteria
Analyze data and perform
calculations on different fields
Queries to retrieve only those
records meeting specified criteria
5
Advantages of Using a Database
Program
Quickly produce professional
reports and forms in a variety of
formats
Multiple data access and data
updating
Elimination of duplicate data
6
Database Terminology
Database Organized collection or
related data commonly
stored as a table in a file
7
Database Terminology
Database Organized collection or
related data commonly
stored as a table in a file
Delete
Remove a record from
database file
8
Database Terminology
Database Organized collection or
related data commonly
stored as a table in a file
Delete
Remove a record from
database file
Edit
Change or delete a record
from the database file
9
Database Terminology
Database Organized collection or
related data commonly
stored as a table in a file
Delete
Remove a record from
database file
Edit
Change or delete a record
form the database file
Field
Smallest item of information
about a record, such as last
name
10
Database Terminology
Query
To ask questions of the
database, which then
displays only those records
meeting specified conditions
11
Database Terminology
Query
Record
To ask questions of the
database, which then
displays only those records
meeting specified conditions
Collection of related fields,
such as Social Security
number, first name and last
name
12
Database Terminology
Report
Printed and formatted
presentation of specified
fields of data for specified
records in the file
13
Database Terminology
Report
Search
Printed and formatted
presentation of specified
fields of data for specified
records in the file
Locate a specific record in a
file
14
Database Terminology
Sort
Arrange a file’s records in a
specified order
15
Database Terminology
Sort
Table
Arrange a file’s records in a
specified order
Collection of data organized
in columns (fields) and rows
(records)
16
Loading Access 97
Start
Microsoft Access
Choose
Blank database
Open an existing database
17
Planning a Database
Design of database tables
Number of tables
Data tables will contain
How tables will be related
18
Database Development
Plan
understand purpose,
data it will contain,
output needed
19
Database Development
Plan
Create
understand purpose,
data it will contain,
output needed
the tables by creating
the structure
20
Database Development
Plan
Create
Enter Data
understand purpose,
data it will contain,
output needed
the tables by creating
the structure
to complete each
record
21
Database Development
Plan
Create
Enter Data
Edit
understand purpose,
data it will contain,
output needed
the tables by creating
the structure
to complete each
record
correct spelling and
typing errors, change of
table structure
22
Database Development
Form Design
creating a form for
easier data entry
23
Database Development
Form Design
Data
Analysis
creating a form for
easier data entry
queries for selecting
specified data, or
viewing data in a
specified format
24
Database Development
Report
Design
for printing in a
professional and
attractive format
25
Database Development
Report
Design
Preview and
Print
for printing in a
professional and
attractive format
previewing onscreen
how document will
appear printed
26
Creating a Database
Choose
Blank database, or
Database window appears - central
window for
Selecting a current object, or
Creating a new object
27
Database Window
28
opening screen
29
Creating a Table
Defining a structure
Structure elements
Fields
Data types
Field properties
Primary Key
Saving the Table Structure
30
Defining a Structure
Defining a structure to contain data
Three main ways to create a table
Datasheet View
Design View- The method we will use in
class
Table Wizard
31
32
Table Design View
33
Structure Elements - Fields
Fields - smallest unit of information
about a record
Field name - identifies data stored in
the field
Up to 64 characters long and can
consist of letters, numbers, spaces
and special characters
Examples: Last Name, First Name,
Address, Phone Number, etc.
34
Structure Elements - Data Types
Text
words, combinations of
words and numbers and
numbers not used in
calculations
35
Structure Elements - Data Types
Text
Memo
words, combinations of
words and numbers and
numbers not used in
calculations
variable length text, up
to a maximum of 65,535
characters
36
Structure Elements - Data Types
Text
Memo
Number
words, combinations of
words and numbers and
numbers not used in
calculations
variable length text, up
to a maximum of 65,535
characters
digits for calculations
37
Structure Elements - Data Types
Text
Memo
Number
Date/Time
words, combinations of
words and numbers and
numbers not used in
calculations
variable length text, up
to a maximum of 65,535
characters
digits for calculations
any valid date or time
38
Structure Elements - Data Types
Currency
numbers formatted to
display $ sign and
decimals
39
Structure Elements - Data Types
Currency
AutoNumber
numbers formatted to
display $ sign and
decimals
unique sequential
number, automatically
incremented with
addition of new records
40
Structure Elements - Data Types
Currency
AutoNumber
Yes/No
numbers formatted to
display $ sign and
decimals
unique sequential
number, automatically
incremented with
addition of new records
accepts only Yes/No,
True/False, On/Off
entries
41
Structure Elements - Data Types
OLE Object
object, such as picture,
sound, video,
spreadsheet, linked to,
or embedded in table
42
Structure Elements - Data Types
OLE Object
Hyperlink
object, such as picture,
sound, video,
spreadsheet, linked to,
or embedded in table
hyperlinked pathway to
object, document, Web
page, etc.
43
Structure Elements - Data Types
OLE Object
Hyperlink
Lookup
Wizard
object, such as picture,
sound, video,
spreadsheet, linked to,
or embedded in table
hyperlinked pathway to
object, document, Web
page, etc.
displays list of options
you choose from
another table in the
database
44
Structure Elements - Field
Properties
Set of characteristics associated
with each field
Each data type has different set
Setting them enhances the way the
table works
45
Structure Elements - Field Properties
Field size
Sets maximum number
of characters allowed
46
Structure Elements - Field Properties
Field size
Format
Sets maximum number
of characters allowed
how data displays in a
table and prints
47
Structure Elements - Field Properties
Field size
Format
Input Mask
Sets maximum number
of characters allowed
how data displays in a
table and prints
simplifies data entry by
controlling what data is
required in a field and
how the data is to be
displayed
48
Structure Elements - Field Properties
Caption
specifies field label
other than field name
49
Structure Elements - Field Properties
Caption
specifies field label
other than field name
Default Value automatically fills in a
certain value for this
field in new records
added to table
50
Structure Elements - Field Properties
Caption
specifies field label
other than field name
Default Value automatically fills in a
certain value for this
field in new records
added to table
Validation
limits data entered in a
Rule
field to that meeting
certain requirements
51
Structure Elements - Field Properties
Validation
Text
specifies message to be
displayed when
associated Validation
Rule not satisfied
52
Structure Elements - Field Properties
Validation
Text
Required
specifies message to be
displayed when
associated Validation
Rule not satisfied
whether or not a field
must contain data
53
Structure Elements - Field Properties
Validation
Text
Required
Allow Zero
Length
specifies message to be
displayed when
associated Validation
Rule not satisfied
whether or not a field
must contain data
whether or not no entry
in field is allowed
54
Structure Elements - Field Properties
Validation
Text
Required
Allow Zero
Length
Indexed
specifies message to be
displayed when
associated Validation
Rule not satisfied
whether or not a field
must contain data
whether or not no entry
in field is allowed
sets a field as an index
field, speeds up
searches
55
Structure Elements - Primary Key
A field that uniquely identifies each
record
Social Security number field a
popular example
Most tables have them
Prevents duplicate records from
being entered
56
Structure Elements - Primary Key
Controls order of records displayed
in table
Used as a link to other tables in a
database
57
Saving the Table Structure
In the Save As dialog box, enter new
table name
Table saved and included within the
database file
58
Views
Design view
used to create a table,
form query or report
59
Views
Design view
used to create a table,
form query or report
Datasheet view row and column view
of data in tables, forms
and queries
60
Views
Design view
used to create a table,
form query or report
Datasheet view row and column view
of data in tables, forms
and queries
Form view
displays records in a
form
61
Views
Design view
used to create a table,
form query or report
Datasheet view row and column view
of data in tables, forms
and queries
Form view
displays records in a
form
Preview
displays a form, report
or datasheet as it will
appear when printed
62
Entering Data
For tables, enter in datasheet view,
or Form View
Enter data carefully and accurately
It will appear just as typed
63
Entering Data
Data form should be consistent
If upper and lower case, don’t add
some records in all caps
No blanks before or after a field entry
Be consistent with abbreviations, like
St. for Street, Ave. for Avenue
64
Adjusting Column Width
Column width size of each field
column in Datasheet View
Not the same as the field size
Does not affect on amount of data to
be entered
Does affect amount of data that can
be seen on screen
Default width is 15.7 characters
65
Adjusting Column Width
Quick method for one column, click
and drag on right column border line
when mouse pointer is a two-headed
black arrow
For all columns
Highlight column headings
Click and drag on one right column
border line changes all widths
commensurately
Double-clicking for Best Fit
66
Adding Records in Data Entry
Move to blank row at bottom
Click on new record button at
bottom of window
Click on Record, Data Entry
67
Previewing and Printing the Table
File, Print Preview to view how table
will appear printed
File, Page Setup to change
orientation, if desired
Print
68
Closing and Opening a Database
File, Open
File, Close
69
Class on Thursday: ACCESS
DEMO
ACCESS Homework can be obtained
from www.csulb.edu\~athomaso
Excel homework due today.
70