Transcript Class #1 - Faculty

Your Tour Guide is Jim Provensal
What We Will Cover

Introduction to MicroSoft Access
What
is a database
What is a “Relational” Database


The Major Steps and elements of a
MicroSoft Access Database
A Tour of the “NorthWind Traders”
Database
What We Will Cover
In this session, we will explore the
elements of MicroSoft Access,
and in the following sessions, we
will be building a database
application step-by-step using
these elements.
We Will Continue to explore
Building a database with Access

Defining Tasks

Defining the database

Creating the database
We Will Continue to explore





Fields
Primary Keys
Validation Rules
Relationships
Indexes and database limitations
We Will Continue to explore





Modifying a database design
Inserting & changing fields
Changing attributes and primary
Keys
Splitting tables
And More on Relationships
We Will Continue to explore



Using and viewing datasheets
Modifying the datasheet
sorting, searching and printing
a datasheet
We Will Continue to explore





Select Queries And Query Wizards
The basic QBE Grid (Query by Example)
Calculated Fields
Expressions in Queries
Criteria in Queries
We Will Continue to explore
Because Queries are the heart of
MicroSoft Access we will continue
exploring queries and their uses.
We Will Continue to explore
Special & Action Queries
 Total & Crosstab Queries
 Make Table Queries
 Append Queries
 UpDate and Delete Queries
We Will Continue to explore
An introduction to basic
form design and setting
object properties
We Will Continue to explore
Customizing Forms
Calculated fields
Subforms
We Will Continue to explore
Report building
from scratch and
using Wizards
and Sorting & Grouping
Before we continue we need to cover some
administrative items.





Course Syllabus
A Discussion on the Student Disks
E-mail Addresses
Introductions
Questions?
What is a Database?
In the simplest sense,
a database is a collection of records
and files that are organized
for a particular purpose!
Microsoft Access as an RDBMS
Microsoft Access is a fully functional
Relational Database Management
System



Microsoft Access is a database.
This means that it is a program that will
store lists of information.
These lists are called “Tables”.
Microsoft Access is a toolbox that will assist the user
in creating a system that will automatically manage
the information stored in the tables.
This means that Access
is not only a database,
but a “Database Management System”.
Since some lists (or tables) contain information that
is related to other lists, the user can tell Access which
lists are related and what piece of information in each
list should be used to connect each list.
This means that Microsoft Access is a
Relational Database Management System.
Main Functions of a Database




Data definition
Data manipulation
Data control
Data reporting
Main Functions of a Database
Data definition
You can define what data will be stored in your database,
the type of data (for example, numbers or characters),
and how the data is related. In some cases, you can also
define how the data should be formatted and how the
data should be validated.
Main Functions of a Database
Data manipulation
You can work with the data in many ways.
Select which data fields you want, filter the data
and then sort it.
You can join data with other related information.
You can Summarize (group, subtotal and total) the
data.
Main Functions of a Database
Data control
You can define who is allowed to read, update,
or insert data. In many cases, you can also define
how data can be shared and updated by multiple
users.
Main Functions of a Database
Data reporting
You can create and save “Pre-defined” report
formats that at a click of the mouse can produce
up to the minute reports showing (in most cases)
data that is within seconds old.
What is MicroSoft Access?
Access is a very powerful “Tool Box” that
will allow you to not only “Collect” data,
“Store” data, “Manipulate” data, and
“Report” data, but it will also allow you as a
“Non-Programmer” to create actual
applications (programs) for others to use.
These applications in most cases can be put
together quite quickly and made
to look very professional.
The Major Steps of a MicroSoft
Access Database






Tables
Queries
Forms
Macros
Reports
Modules
1) TABLES: The basic element of a data base used only
to store raw data.
2) QUERIES: Sets of data consisting of some
or all data from one or more
tables with related data.
3) FORMS: Screens that are linked to either tables
or queries and are used for data entry
data editing and controlling what data
is accessed and how it looks.
4) REPORTS: Pre defined and pre designed
output documents generally
used for hard copy output.
5) MACROS & Modules: Tools used for automation of tasks.
A collection of related tables is called a
database, or relational database. Our
Case Study’s database will contain two
related tables:
The Customer table, which has
already created, and
The Order table which you will
create in Tutorial #2.
Sometimes you might want information
about customers and the orders they
placed. To obtain this information you
must have a way to connect records in
the Customer table to records in the
Order table.
You connect the records in the separate
tables through a common field that
appears in both tables.
Figure 1-2 shown on page AC-1.05 of your
book shows that each record in the customer
table has a field named Customer Number,
which is also a field in the Order table.
Each Customer Number in the Customer table
must be unique, so that you can distinguish
one customer from another and identify the
customer’s specific orders in the Order table.
The Customer number field is referred to as
the primary key of the Customer table. A
primary key is a field, or a collection of fields,
whose values uniquely identify each record in
a table.
In the Order table, the Order Number field is
the primary key for that table.
When you include the primary key from one
table as a field in a second table to form a
relationship between the two tables, it is called
a foreign key in the second table as shown
above.
Fields
Records
In a table, the rows are called records and the
columns are called fields.
A relational database management system
(DBMS) allows you to create database
structures containing fields, tables, and
relationships.
It lets you easily add new records, change field
values in existing records, and delete records.
It contains a built-in query language, which lets
you obtain immediate answers to the questions
you ask about your data.
It contains a built-in report generator, which
lets you produce professional-looking,
formatted reports from your data.
It provides protection of data through security,
control, and recovery facilities.
An Access Database Consists of:
RAW DATA
STORAGE
TABLE
TABLE
TABLE
RELATIONSHIPS
QUERY
QUERY
CRITERIA
DATA ENTRY
VIEW AND
EDIT
OUTPUT
FORM
REPORT
If you haven’t already powered up
your computer, lets do so now.
Let’s take a look at the basic
elements that we will encounter
when we first create a database
or open an existing one for
modification.
Let’s start Access now and open
a new database.
Title Bar
Menu Bar
Tool Bar
Status Bar
Database Window
Command
Buttons
Object List
Object Buttons
Before we begin working on
our Case Study’s database and
using Access, we need to
understand a few key terms
and concepts associated with
databases.
Before storing data on a computer,
you first must organize the data.
We can do this using a data analysis
technique, identifying the individual
groups of data that will eventually be
called tables and each unique item in
each group that we will call fields.
A field is a single characteristic or
attribute of a person, place, object,
event, or idea. For example, some of
the many fields that Valle Coffee
tracks are customer number,
customer name, customer address,
customer phone number, order
number, billing date, and invoice
amount.
Next, you group related fields
together into subjects, or tables. A
table is a collection of fields that
describe a person, place, object,
event, or idea.
Data Analysis
Identify
Tasks
Task
Analysis
Record
Customer
Orders
v
Data
Analysis
Subject #1
(Tabe)
Subject #2
(Table)
Subject #3
(Table)
The design phase of databases can be
summarized into three steps:
1) Identify the task (keep your focus as narrow
as possible for each task).
2) Identify all the unique pieces of information
that is available.
3) Sort each piece of information gathered into
related groups.
In your syllabus that was handed out a
short time ago, you will find two forms.
A Task Sheet &
a Subject Sheet.
Please find them
now.
Using these forms,
let’s take a few
moments to try a
short exercise that
demonstrates how
we might go about
the initial design
phase of a database.
After the design is
complete on paper it is
time to create the
database and
set up your
tables.
If you exited Access please re-start it now.
Click on the
Blank
Database
Button
On the next screen click on Blank Database then OK
Click on
Blank
Database
Then Click on
the OK Button
Select My Documents
Name the database
Pro.mdb
Then click the
create button
You have now created a new (empty) database and
Your screen should look like this.
Do you remember The Major Steps of
a MicroSoft Access Database?






Tables
Queries
Forms
Macros
Reports
Modules
Here they are. These are
called Objects in Access.
Let’s create a new table in our database.
Then click on the
“New” Button.
First, make sure that the
table tab is selected.
Now, select design view from the dialog box.
We should now have a new table and we are ready to setup
fields in the table. But first, let’s look at the elements of the
table design screen.
Field Name
Description
Data Type
Let’s add our first field.
Type Account Number
in the first Field Name
Row above, then press
the Tab key.
As soon as you enter the data type column you
will notice a pull down selection arrow. You
may either click on this arrow or press the F4
key to see the data types available
You will find a chart of the different data types
available and the properties available for each
data type on page AC 2.05 in your book.
It’s now time to define the fields
that we will be using in our table.
Let’s take a few moments to
create the following fields in our
new table.
Account Number:
First Name:
Last Name:
Address:
City:
State:
Zip:
Phone Number:
Date of Birth:
Text
Text
Text
Text
Text
Text
Text
Text
Date/Time
What is a Primary Key?
To set the primary key
1st click on the
Account Number
Field
Then click on the
Primary Key Button on
the tool bar.
It’s now time to save our new table.
Select File on the Menu Bar.
Then Select Save As since
this is the first time that we
have saved.
Use Table1 (the default)
and then click on the
OK button.
After you have created and saved a table,
it’s time to add data. From the menu bar,
select View and then Datasheet View.
When you first open a datasheet, Access
selects the first field value in the first
record.
The current record symbol appears in the
record selector to the left of the first record.
The current record symbol identifies the
currently selected record.
Clicking a record selector or field value in
another row moves the current record
symbol to that row. You can also move the
pointer over the data on the screen and
click one of the field values to position the
insertion point.
The navigation buttons provide another way
to move vertically through the records.
The Navigation Buttons From Left to Right:
This button will bring you to
the first record
The Navigation Buttons From Left to Right:
This button will bring you to
the previous record
The Navigation Buttons From Left to Right:
This area indicates the currently
active record. You can press the
F5 key to go here and then type
the record # that you would like
to go to.
The Navigation Buttons From Left to Right:
This button will bring you to
the next record
The Navigation Buttons From Left to Right:
This button will bring you to
the last record
The Navigation Buttons From Left to Right:
This button will bring you to
the data entry mode so that
you can enter a new record.
Quick Check Review
Page AC 1.13
Quick Check
Review
Let’s take a few
moments to break up
into discussion groups.
Each group will discuss
the quick check questions on page
AC 1.13. We will then review the
answers at the end of the discussion.
Quick Check
Review
1) A(n) _______ is a single
characteristic of a person, place,
object, event or idea.
Quick Check
Review
2) You connect the records in two
separate tables through a(n) ________
that appears in both tables.
Quick Check
Review
3) The ________, whose values
uniquely identify each record in a
table, is called a _________ when it is
placed in a second table to form a
relationship between the two tables.
Quick Check
Review
4) In a table, the rows are called
_________ and the columns are
called _________.
Quick Check
Review
5) The ________ identifies the
selected record in an Access table.
Quick Check
Review
6) Describe the two methods for
navigating through a table.
Your Tour Guide is Jim Provensal