Transcript MS ACCESS

Introduction to
MS ACCESS
Presented by
Arlene N. Baratang, Ph.D.
Objectives
At the end of this training session you should be able to:
• Understand the three types of relationships: one-tomany, many-to-many and one-to-one;
• Create a new database;
• Create tables for data entry in design view;
• Use, understand, and set data types: Text, Memo,
Number, Currency,
• Date/Time, AutoNumber, Yes/No, Lookup wizard.
• Create and delete a primary key;
• Insert and delete a row.
Questions to Ask Before
Designing a Database:
What does the database need to do?
• What functions need to be achieved?
• Which objects (such as tables, forms) depend on
each other?
• What items are needed for the database?
• Who will use the database?
• How will the output data (report) be generated?
• How will the database be organized?
Access
Database Objects
Table
• The central framework of a database that
stores data in fields (columns) and records
(rows).
Query
• Allows for table inquiries.
A query can change,
delete, add, arrange data
in tables. Also aids
gathering information for
forms and reports.
Form
• Displays and enters
data in a fi tted
format. Forms can
also contain other
nested forms
(subforms).
Report
• Allows for the printing
and print preview of
information such as
labels, lists, form
letters, invoices,
summaries, display
charts, etc. The user
can personalize
reports by adding a
logo or picture,
organizing headers,
details, footers, and
sorting columns.
Page
• Also known as Data Access Page. Allows the publication of a
web page and web access to a database. A page can be
viewed and edited, and the information can be altered.
Macro
• Allows for
automating simple
and common tasks
such as opening
and closing a form,
exporting data,
printing data in a
report, and saving
data.
Module
• Modules are a collection of Visual Basic
declarations and procedures that allow for the
automation and customization of Access,
giving the user more explicit control over
actions.
Naming Conventions
• The Leszynski Naming Convention (LNC),
originally created by Stan Leszynski and Greg
Reddick, developed guidelines called tags to
assist the user to open, edit, and
troubleshootwithout deciphering what is
contained within the object itself.
• File names can be more than one word; however,
do not use underscores or spaces. Instead,
capitalize the first letter of each word.
e.g. tblEmpInfo
Naming Conventions
•
•
•
•
•
•
•
Table
Query
Form
Report
Macro
Module
Database
tbl
qry
frm
rpt
mcr
mdl
dbf
Types of Relationships
in
Microsoft Access
One-to-many
Table 1
1
Table 2
2
3
Many-to-many
One-to-one
Introduction to Database
Concepts
Organizing Data
Field
• A field is a single
characteristics or attribute of a
person, place, object, event, or
idea.
Table
• A table is a collection of fields that
describe a person, place, object,
event, or idea.
Field Value
• The specific value, or content
of a field is called the field
value
Record
• This set of field values is
called a record
Databases and
Relationships
Database
• A collection of related tables is
called a database , or a relational
database.
• You connect the records in the
separate
tables
through
a
common field that appears in
both tables.
Primary Key
• A primary key is a field, or a
collection of fields, whose
values uniquely identify each
record in a table.
Foreign Key
• When you include the primary key
from one table as a field in a
second table to form a relationship
between two tables, it is called
foreign key in the second table .
Relational Database
Management Systems
Database Management
System (DBMS)
• A database management
system (DBMS) is a software
program that lets you create
databases and then
manipulate data in them.
Relational Database
Management System (RDBMS)
• Data is organized as a collection of
tables. A relationship between two
tables in a relational DBMS is
formed through a common field.
RDBMS
• A relational DBMS controls the
storage of databases on disk
by carrying out data creation
and manipulation requests.
Functions of RDBMS :
• It allows you to create
database structures
containing fields, tables, and
table relationships.
Functions of RDBMS :
• It lets you easily add new records,
change field values in existing
records, and delete records.
Functions of RDBMS :
• It contains a built-in query
language, which lets you obtain
immediate answers to the
questions you ask about your
data.
Functions of RDBMS :
• It contains a built-in report
generator, which lets you
produce professional-looking
formatted reports from your
data.
Functions of RDBMS :
• It provides protection of
databases through security,
control, and recovery
facilities.
A company benefits from a relational
DBMS because it allows several
users
working
in
different
departments to share the same data.
More than one user can enter data
into a database, and more than one
user can retrieve and analyze data
that was entered by others.
Starting Access
1. Click the Start button on the taskbar then
point to Programs.
2. Click Microsoft Access. The Access
window appears.
3. To create a new Access database, select
Blank Access database.
Starting Access (con’t)
4. In the Save in box, select the folder where
you want your database to be located.
5. Type the filename of your database on
File name box.
6. Then click Create button.
The
Access and
Database Windows
Access window
Database toolbar
title bar
Database menu bar
Database
List of tables
window
in a database
Open
Object
Design Object
Delete
Object
Create Object
Object
Display Properties
Objects Bar
Object List Pane
Object Group
The Database Window Toolbar
object display properties
To create a table in a design view:
1. Select the Table object on the Objects
bar.
2. Click the Design button on the Database
window toolbar, or
double-click Create Table in Design
View on the list pane.
A BLANK TABLE IN A DESIGN VIEW
Each row in the top half of the Table window
represents a field. The bottom half of the
dialogue box is split in to two sections: the righthand pane displays a short description of the
selected area of the dialogue box, while the lefthand side allows you to set additional properties
for the field selected in the top half of the
dialogue box.
To add a field to a table
There are only two mandatory
properties for defining a field:
the Field Name and Data Type.
DATA TYPE
The data type determines
what field values you can
enter for the field and what
other properties the field will
have.
DATA TYPES FOR FIELDS
DATA TYPE:
Text
DESCRIPTION: Use for names, addresses,
descriptions, and fields containing
digits that are not in used in
calculations.
FIELD SIZE: 0 to 255 characters
DATA TYPES FOR FIELDS
DATA TYPE:
Memo
DESCRIPTION: Use for long comments and
explanations.
FIELD SIZE: 1 to 64,000 characters
DATA TYPES FOR FIELDS
DATA TYPE:
Number
DESCRIPTION: Allows positive and
negative numbers as field values.
FIELD SIZE: 1 to 15 digits
DATA TYPES FOR FIELDS
DATA TYPE:
Date/Time
DESCRIPTION: Use for valid dates and times
and can perform calculations on dates
and times.
FIELD SIZE: 8 bytes
DATA TYPES FOR FIELDS
DATA TYPE:
Currency
DESCRIPTION: Allows field values similar to
number data type.
FIELD SIZE: Accurate to 15 digits on the left
side of the decimal separator and to 4
digits on the right side
DATA TYPES FOR FIELDS
DATA TYPE:
AutoNumber
DESCRIPTION: Access automatically inserts
a value in the field as each new record
is created.
FIELD SIZE: 9 digits
DATA TYPES FOR FIELDS
DATA TYPE:
Yes/No
DESCRIPTION: Limits field values to yes or
no, on or off, or true or false.
FIELD SIZE: 1 character
DATA TYPES FOR FIELDS
DATA TYPE: OLE Object
DESCRIPTION: Allows field values that are
created in other programs as objects.
These objects can be linked or
embedded.
FIELD SIZE: 1 gigabyte maximum
DATA TYPES FOR FIELDS
DATA TYPE:
Hyperlink
DESCRIPTION: Consists of text or
combinations of text and numbers
stored as text and used as a
hyperlink address. It helps you to
connect your application easily to
the Internet or an Intranet.
FIELD SIZE: Up to 64,000 characters
DATA TYPES FOR FIELDS
DATA TYPE:
Lookup Wizard
DESCRIPTION: Creates a field that lets you
lookup a value in another table or in a
predefined list of values.
FIELD SIZE: Same size as the primary key
field used to perform the lookup.
Field Size
The field size property defines a field
value’s maximum storage for text,
number, and AutoNumber fields only.
The other data types have no field
size property because their storage
size is either a fixed, predetermined
amount
or
is
determined
automatically by the field value itself.
Field Size Properties
Byte
Stores
whole
numbers
(numbers with no fraction) from
0 to 255 in one byte.
Field Size Properties
Integer
Stores whole numbers from –
32,768 to 32,768 in two bytes.
Field Size Properties
Long Integer(default)
Stores whole numbers from –
2,147,483,648 to 2,147,483,648
in four bytes.
Field Size Properties
Single
Stores positive and negative
numbers to precisely seven
decimal places and uses four
bytes.
Field Size Properties
Double
Stores positive and negative
numbers
to
precisely
15
decimal places and uses eight
bytes.
Field Size Properties
Replication ID
Establishes a unique identifier
for
replication
of
tables,
recording and other objects
and uses 16 bytes.
Field Size Properties
Decimal
Stores positive and negative
numbers
to
precisely
28
decimal places and uses 12
bytes.