Transcript All You Ever Wanted To Know About Access (that we can cover in three hours) Instruction and Tables Developed by Bob Thurman, Richardson ISD Instruction.

All You Ever Wanted To Know
About Access
(that we can cover in three hours)
Instruction and Tables Developed by
Bob Thurman, Richardson ISD
Instruction Edited by Terence Peak,
UIW
What You Will Learn Today
Defining Tables, Queries, Reports and Forms
Database Planning
Developing Tables
From Table Templates
Manually
From External Data
In the Design View
What You Will Learn Today
Setting the Primary Key
Creating Relationships
Running Queries
Creating Forms
Generating Reports
Making Labels
Creating Charts
Definitions
Database: Tables, forms, queries, reports in
one file.
Record: A set of Data about a person or thing.
Field: A category of information that pertains
to all records (in a column format).
Value: A single piece of data.
Source- New Horizons source booklet ; Access Level 1, Course Edition11
Definitions
Table: A group of records and fields stored as rows
and columns.
Query: A question the searches a database for
information.
Form: A document used to input table data, arranged
in an order specified by the user.
Report: Graphic interface used to display the output
of a table or query.
Primary Key: The Primary Key is a combination of
fields that contain values that uniquely define records.
Source- New Horizons source booklet ; Access Level 1, Course Edition11
Planning a Database
Identify the Purpose of the Database
What do you want the database to tell you.
What kinds of data will be in the database.
Describe who will use the database.
Review Existing Data
Existing databases, spreadsheets, documents, invoices, etc.
Determine Fields
Develop fields to generate information that is pertinent to
the database.
Fields should contain smallest meaningful value.
Source- New Horizons source booklet ; Access Level 1, Course Edition11
Planning a Database
Group Fields into Tables
A table should contain information on ONE SUBJECT.
Too many tables is not a problem, too few is.
Normalize Data
Once a table is complete, enter data to determine
whether or not the data yields good information.
This process assists in determine if you are asking for
information as the smallest meaningful value.
Determine Primary Key
The Primary Key is a combination of Fields that contains
values that uniquely define records.
It establishes relationships between tables.
Source- New Horizons source booklet ; Access Level 1, Course Edition11
Creating a Table
Blank Database
Open Access
Click on Start
Click All Programs
Click Microsoft Office
Click Access
When the page
opens, locate and
click New Blank
Database
Blank Database
Look on the right side of your
screen
Create a FileName
Each word of the File Name is
capitalized. Spaces may be
used (in prior versions of
Access, spaces were not
allowed).
This format us used for
queries, forms and reports.
Click Create
Table
A Table is a group of records and fields stored
in rows and columns In a table, a row is
referred to as a record, and a column is
referred to as a field.
A Record is set of Data about a person or thing.
A Field is a category of information that pertains to all
records.
New Table
The new table opens looking like the illustration
above.
As with all other Office 2007 products, all of the
database functions are accessed through the Ribbon
Click the Create tab to create a table
New Table
In previous versions of Access new tables
were created using one of three methods:
Manually in the Design View
Using the Table Wizard
From external data
In Access 2007, tables can be created:
Manually
Using a Table Template
From (or as) a SharePoint list
From external data
In the Design View
Manually Create a New Table
Click the Create tab
Click Table
Manually Create a New Table
By default the table has
two fields ID and Add
New Field. (ID will be
used for the Primary Key)
Double-click Add New
Field to name the field
Field names should
accurately describe the
data entered into the
field
Fields should contain
smallest meaningful
value
Manually Create a New Field
To add a additional
fields, move the mouse
to the edge of the field
and click
Click the New Field icon
and a new field
template will appear
Add Field Template
There are several
categories of preformatted fields,
Double-click on the name
of the field template to
add the field
Continue the process until
all of the necessary tables
have been added.
Templates
A table template is an empty table that you can start using asis, or modify to suit your needs. Templates are designed to be
used with Windows SharePoint Services 3.0
These are the available templates:
Contacts A table for managing business contact
information
Tasks A table for tracking tasks
Issues A table for tracking issues
Events A table for managing events
Assets A table for managing business assets
After you create a table by using a table template, you may
want to add fields by using field templates.
http://office.microsoft.com/en-us/access/HA012242511033.aspx?pid=CH100645691033
Create a New Table from a Template
Click the Create tab
Choose Table
Templates
Choose Contacts
Create a New Table from a Template
This creates a preformatted table
containing contact
information, (first
name, last name, etc.)
The table can be edited
to remove columns
(fields) that are
unnecessary
Notice that ID is the
first field
The ID Field
The ID field is crated by
Access to provide a
uniquely defined record
that can be used as the
Primary Key
A number is automatically
assigned to the ID field
once information is
added to other fields
within the table
The records will be
numbered sequentially
Editing a Field (Column)
There are two choices for
editing fields in a table;
rename or delete
Right-click on the field
name
Choose rename or delete
If renaming, remember o
name the field so that the
name accurately describes
the information in the field
Create a Table from External Data (Excel)
Click the External Data
tab
Choose Excel (Data will
be imported from Excel)
Create a Table from External Data
On the next page, click
Browse and locate the
Excel file (Data.xls)
Choose Import the
source data…
Click OK
Create a Table from External Data
On the next page, click
Next
Click the box “First Row
Contains Column
Headings”
Click Next
Click Next again
Create a Table from External Data
Check the box “Let Access
choose the primary key”
This will add an additional
field (ID), and number it to
create a primary key.
If a primary key is not
necessary, check No primary
Key
Click Finish
Click Close
Edit and Create tables in Design View
To edit the database
in the design view,
click on table
Addresses: Table
Click the Home tab
Click the Design
View Icon
Edit Fields in Design View
Field Names are the
Column headings in a data
base. For example ID, First
Name and Last Name are
Field Names can me edited
in Design View
Double-click on Zipcode to
select the field, and type Zip
Code
Repeat to change M/F to
Gender
Note the Primary Key
Symbol in the ID Field
Edit Fields in Design View
Data Types denote the
types of information
(number, date/time,
text) contained in a Field
Data Types can be
edited
Click on Zip Code
Click the Data Type
Field
Click on the Drop Down
List arrow
Choose Text
Edit Field Properties in Design View
Field Properties may also
be edited for content. Field
Properties are below the
Fiend Name and Data type
tabs
Select Zip Code
Click the General Tab
Double click Field Size to
change the Field Size to 20
Edit Field Properties in Design View
Double-click required and
change Required to Yes
These edits now limit the
size of the Zip Code field
to 20 characters
Since Yes is selected in
the Required field, the
field may not be left blank
when adding data to the
table
Input Mask
The Input Mask allows
for text added to a table
in a pre determined
format
Select Zip Code
Click the General tab
Choose Input Mask and
click on the toggle
Click Save on the popup menu
Input Mask
The Input mask will format
numbers and spacing for
Phone Numbers
Zip Codes
SSNs
Date
Time
Choose an Input Mask
Click Finish
Zip codes will now be
entered as a 10-digit format
Input Mask
Add a second Input
Mask so that the
Phone Number is in
the proper format.
Be sure to change the
Data Type to Text
Change the Field Size
to 15
Create a Table in Design View
Click the Create tab
Choose Table Design
Create a Table in Design View
The Field Name and
Data Type columns
open along with
Table Properties
Double click Field
Name tab to name
the field Student ID
Change Data Type to
Auto Number
Create a Table in Design View
Add the following Field
Names and Data Types to
the table
Field Name
Data Type
StudentID
Auto Number
ExerciseLogID
Number
WorkoutDate
Date/Time
Hours
Number
Minutes
Number
Seconds
Number
Create a Table in Design View
Click Save
Name the table
Workout
Click OK.
If asked to create the
Primary Key click Yes
Create a Table in Design View
The primary key may
also be set by rightclicking in the column
next to the field name
Choose Primary Key
from the list
Create a Table in Design View
Rows may also be
added or removed by
right-clicking in the
column next to the
field name
Click Insert Rows or
Delete Rows
The Primary Key
The Primary Key is a combination of Fields that
contains values that uniquely define records.
It is possible to set multiple Primary Keys is a table.
Relationships require a Primary Key to be set.
Once you designate a primary key for a table, Access
will prevent any duplicate or Null values from being
entered in the primary key fields.
Types of Primary Keys
AutoNumber: This Field can be set to automatically
enter a sequential number as each record is added to the
table. This is the simplest way to create a primary key.
Single-field primary keys: If you have a field that
contains unique values such as ID numbers or part
numbers, you can designate that field as the primary key.
Multiple-field primary keys: in situations where you
can't guarantee the uniqueness of any single field, you
may be able to designate two or more fields as the
primary key. Common when a table is used to relate two
other tables in a many-to-many relationship.
Change Primary Key
Open the Addresses Table
Notice that ID is the Primary
Key, it needs to be changed
to SSN
Right-click on the Key Icon
Click Primary Key
Move to SSN
Right-click on the Key Icon
Click Primary Key
There must be data in new
Primary key field
Relationships
Types of Relationships
One to One: An association between two tables
in which one record a table relates to only one
record in the other table.
One to Many: An association between two
tables in which one record in a table can relate
to many records in the other table.
Many-to-Many : An association between two
tables in which one record in either table can
relate to many records in the other table.
Creating Relationships
Using the current
Database Student Data –
Tables Only
Click the Database Tools
tab
Click Relationships
All tables in a database
must be closed to create
relationships
One-to-One Relationships
The Show Table menu
opens. Be sure that the
Tables tab is selected
Click on the Students and
click Add
Click Workout and Click
Add
Both tables should be in
the Relationship Window
Close the Show Table
menu
One-to-One Relationships
Click and drag from
StudentID in the
Students table to
ExerciseLogID in the
Workout table. Note that
both fields are Primary
Keys
The relationship type
should be One-To-One
Click Create
Click OK
Editing Relationships
To remove a
relationship, Rightclick on the
relationship line
Click Delete
Click Yes
The relationship is
removed
One-to-Many Relationship
Click on the Relationships icon
on the toolbar and create a
relationship between
StudentID in the Students
table and StudentID in the
Workout table
Click Enforce Referential
Integrity & both boxes below
it
The relationship type is One to
Many
Click Create
One-to-Many Relationship
Notice the “infinity”
symbol-denoting a
One-to-many
relationship
Save your work, then
close the
Relationships
window
Relationships
A relationship works by matching data in key
fields - usually a field with the same name in both
tables.
In most cases, these matching fields are the
primary key and a foreign key A foreign key
indicates how the tables are related in the other
table.
If you want to change the Primary Key, you must
first delete (remove) the relationship between
the tables.
Queries
Queries
A Query is a question the searches a database
for information.
If a relationship is established, a query can be
run from two or more databases.
Queries can be fine-tuned to;
Give specific information about a data field.
Summarize information over years, days or months.
Review multiple tables to find duplicate information.
Running Queries
Click on the Create
tab
Click Query Wizard
(far right on the tab)
The Query menu
opens, choose
Simple Query
Wizard
Click OK
Query
Choose Table: Workout ,
click on the Tables/Queries
dropdown menu
Select the WorkoutDate
and Distance fields from the
Workout table By clicking
the field name, then the >
The field names will appear
in Selected Fields
Click Next
Query
Click Summary
Click Summary
Options
Query
Click Sum
Click OK
Click Next
Query
Click Year
Click Next
Query
Name the query Workout
Check Open the query to
view information
Click Finish
Check your results, then
close the window
The result is a table that
summarizes all of the runs
for each year by total
distance
Query in the Design View
Using the Design
View to create
quires provides
additional flexibility.
Queries can be
modified and to
generate specific
results.
Click Query Design
Students by Zip Code Query
Select the
Students table
Click Add
Click Close
Students by Zip Code Query
Click and drag
these fields;
StudentID,
FirstName,
LastName, and
PostalCode to the
first four columns
Students by Zip Code Query
To sort by Zip Code,
click under
PostalCode in the
Sort row
Select Ascending.
There should be
check marks under
each field.
Students by Zip Code Query
Click on Run! on the
ribbon to run the query.
Click Save
Save as Zip Code
Click OK
The result of this query is
a list of all of the students
sorted by zip code, lowest
to highest
2001 Query
Click Query Design
Click Open.
Select the Workout table
Click Add
Click Close
2001 Query
Click and drag WorkoutDate and Distance to the first
two fields under WorkoutDate
Next to Criteria type Between 01/01/01 and 12/31/01
Save the query and run it.
The result is a list of all runs for 2001
Long Runs Query
Click Query Design
Double-click the Students and Workout tables
Click Close
Click the fields window to open the dropdown
menu
Double-click Students:FirstName, and
Students:LastName
Click and drag Workout:WorkoutDate and
Workouts:Distance to the next two columns.
Long Runs Query
In the Criteria Row, in the Distance Field, type >10
Save
Run the query
What is the result?
This or That Query
Click Query Design
Double-click the Students table
Click Close
Click and drag FirstName, LastName, Address,
City, StateorProvince, and PostalCode from
the Students table to the first seven columns.
This or That Query
Under LastName type Thurman in the first Criteria
row and Jones in the second Criteria row.
Under PostalCode type 75243 in the first Criteria
row and 75080 in the second Criteria row
Save your work
Run the query
The result is a list of everyone named Thurman or
Jones who lives in the 75243 or75080 zip code
Find Duplicates in Queries
Click Query Wizard
Select Find
Duplicates Query
Wizard
Click OK
Find Duplicates in Queries
Select the Workout
table
Under View, click
Table
Click Next
Find Duplicates in Queries
Select Distance
Click Next
Find Duplicates in Queries
Click >> to select all
fields
Click Next
Click Finish
What is the result?
Unmatched Query
Click Query Design
Select Find
Unmatched Query
Wizard
Click OK
Students Without Matching Workout Query
Select the Students
table
Click Next
Select the Workout
table
Click Next
Tables should be
selected for both
tables
Students Without Matching Workout Query
Click >> to select all
fields
Click Finish
Close all Queries
Forms
Forms
A Form is document used to input add data
to a table. It can be arranged in an order
specified by the user.
Forms can be developed so that someone
accessing a database can input data into
table, without being able to access the table.
Form Wizard
On the Create tab,
choose More Forms
Click Form Wizard
Form Wizard
Select the Students
table, then select all
fields by clicking >>
Select the Workout
table, then select all
fields by clicking >>
Form Wizard
Deselect the
StudentID field in
the Workout table
by clicking on it, then
on <
Click Next
Form Wizard
Accept the defaults
by clicking Next
twice.
Choose Flow
Click Next
Click Finish
Forms
For the purposes of this class, this is a brief
exercise in creating a form. Forms can be
much more complicated and can be modified
to perform a significant number of functions
To see some of the options available in a form
open BikCitLuis .mdb and open the BikeCity
Form
Reports
Reports
A Report is the graphic or hardcopy output of
a table or query.
Reports are in a more legible format than a
query
Reports can be formatted to be easier to read
and understand
Data output from a report can be sorted and
grouped according desired output
Output can be as labels or as a chart
Report – Workout Total By Week
On the Create tab
Click Report Wizard.
Report – Workout Total By Week
From the Students table,
select FirstName and
LastName,
From the Workout table,
select WorkoutDate and
Distance
Click Next
Accept the defaults
Click Next
Report – Workout Total By Week
Double-click
WorkoutDate as the
Grouping Level
Click on Grouping
Options
Report – Workout Total By Week
Select Week as the
grouping interval
Click OK
Click Next
Report – Workout Total By Week
Click on Summary
Options, choose
either Detail and
Summary or
Summary Only
Click OK
Click Next
Report – Workout Total By Week
Choose Landscape
Orientation
Choose a Layout
Click Next
Choose Style
Click Next
Name the report
Workout Total By Week
Click Finish
Labels
Click on the Students
Table
Click Labels in the
Reports Group
Labels
Choose 5260 as a
Label type
Click Next
Accept the defaults
Click Next
Labels
Add the following fields
in this format:
FirstName > <SP> >
LastName
<R> > Address
<R> > City <SP> > State
<R> > ZipCode
Click Next
Labels
Sort by Zip Code
Click Next
Click Finish
Viewing Tables, Queries, Forms & Reports
Click the down arrow
next to Tables
Select All Access Objects
Questions?
Terence Peak, M.Ed.
Coordinator of Technology Training
Blackboard Certified Trainer
The University of the Incarnate Word
(210) 829-3920
[email protected]