Transcript Document

WFM 5201: Data Management and Statistical Analysis © Dr. Akm Saiful Islam
WFM 5201: Data Management and
Statistical Analysis
Lab-3: Introduction to MS Access XP
Akm Saiful Islam
Institute of Water and Flood Management (IWFM)
Bangladesh University of Engineering and Technology (BUET)
June, 2008
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Introduction to MS Access XP


Concepts of Database
Access XP Basic Features
 Tables
 Forms
 Reports
 Queries
 Update
Information on Tables, Forms,
Reports, and Queries
WFM 5201: Data Management and Statistical Analysis © Dr. Akm Saiful Islam
WFM 6103: hydrologic Information System © Dr. Akm Saiful Islam
Microsoft Access Window

Open Access

Start-Programs-Microsoft Access
 Double-click on an Access file (Address)
Objects Operations
Places Bar
Viewing Objects Properties
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Concepts of Database

Database is a collection of records and files that are organized
for a particular purpose.





A table in MS Access is a set of records with related information
displayed in rows and columns.
A database in MS Access consists of one or more tables. The
tables may be related.
A field is a basic fact of a record (or data element).


Name, address,…
A primary key is a field that identify a single record.


A student record book with addresses and grades
An inventory with software, books, and hardware
In the primary key field, every record has a unique number. (ID field)
A record is a set of fields:

A person, a student, a book
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Microsoft Access Objects

Tables


Queries


Source information with Columns (fields), rows (records)
Subset of information with certain criteria extracted from
table (s).
Forms

Display one record in the window


Convenient for entering and updating data.
Reports

Display records with selected fields in a formatted layout.

Display multiple records in a page divided by a certain group.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Microsoft Access Objects

Pages


Macros


Display records in form of web pages.
A set of commands that are executed automatically
one after another. Macros are used to automate the
performance of any repetitive task.
Modules

Provides a greater degree of automation through
programming in Visual Basics for Applications (VBA)
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
An Access Database File
Form1A… Form1N
Report1A… Report1N
Table1
Query1A… Query1N
Form2A… Form2N
Table2
Report2A… Report2N
Query2A… Query2N
TableN
Form3A… Form3N
Report3A… Report3N
Query3A… QueryAN
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
An Access Database File
Form1… FormN
Table1
Report1… ReportN
Table2
TableN
Query1… QueryN
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Table-Datasheet View

Double-click a table, you are in datasheet view.

Add, edit, or delete records
Field Caption
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Table-Design View

Define the table initially and to specify the fields it will
contain.
Define Field Name
Define Field Type
Design View:
Form, Query, and Report
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Print or Change Properties of
Tables, Forms, Reports, Queries

In the database window
 Right-mouse
click an
object (report, form,
query, page)






Print
Cut
Copy
Email
Delete
Rename
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Plan and Create a Database
The purpose of the database.
 Tables to create (address)
 Fields in each table (Name, phone,…)

 Smallest

meaningful value possible.
An ID field may serve as the primary key
field.

The primary key for a table is a unique
identifier for each record in the table.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Database


Start-Programs-Microsoft Access
Select Blank Database on the Task Pane
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Save the Database




Type the name
of the
database file.
If you need to
save the file
into a new
folder, you
click on and
type in the
folder name.
Click on
Type the file
name.
Click
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Table in Design View

Double-click

For each field

Type the field name.
 Select a data type .
 Type the Caption.


Select “student ID” field
Click on
to set a primary
key

Click on
to save the table
Type in the table name
Click on

Click


to input data.
Use “Tab” or arrow keys to navigate.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Field Name and Caption

Field Name

Used in reference of other objects, calculations, or visual
basics programming.
 Should be short and concise.

Caption




The column header in a table.
Can be descriptive and meaningful.
When it is blank, the Field Name is used as the column
header.
Advantage of using the Caption

After the database is built with tables, forms, reports, and
queries, if you would like to change a column header, you may
change the Caption not the field name.
WFM 5201: Data Management and Statistical Analysis © Dr. Akm Saiful Islam
Data Input in the Table


Click on
to go to the datasheet view when you
use Design View to create a new table.
Type in information in the cells

“tab” key,
or
to navigate to a different field.
keys to a different record.

Adjust the column width




Move the mouse to the boarder between the two columns until a
sign shows, and drag the boarder to adjust the column width.
Double-click the boarder between the two columns and the column
width will be adjusted automatically.
Delete a record, select any cell of the record and click
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Add a Field in a Table Created In Design
View

Add a “parent’s name” field before “address” field



Select the “address” field, go to Insert and select “rows”
Type the “parents’ name” for the field name.
Click
to update the table set up.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Reorder a Field

In the Design View of a table

Drag the field and move it to a
different row.

In the Data View of a table

Drag the field header and move it to a
different column.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Table by Using Wizard







Double-click on
Select the category of the wizard, “business”
Select “Student” in the “sample table” window.
Select “StudentID” in the Sample Fields window, click
on
Click on
, type in the new name “ID”.
Click on
Select “Firstname”, “Lastname”, and other fields
individually and use
to move the field to the
selected field window.
Click on
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select a Wizard and Fields
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Name the Table


Type in the name of the table
Select
, click on
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select a Primary Key Field

Select “ID” as the primary key, Next
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Set Up Data Input
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Modify the Table

You may want to change StudentID from Number to Text data
type if you use BannerID. To add @ for the Banner ID, type
“@00000000” in the Input Mask box.
@00000000
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Table by Entering Data

In database window


Double-click
In the Datasheet view




You need to set up a primary key by going to Design
View



Double-click “field1”, type a field name “ID”.
Double-click “field2”, type a field name “First Name”
… repeat the above steps until you type all the fields.
Click
Click
Click
, select “ID” field cell and click
to save the table.
to Datasheet View to input data.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Navigating and Updating Data in the Table
Save Data-Access automatically save a table as soon as
you move to the next record.
Previous Record
Next Record
Add a New Record
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Updating Records in a Table

Adding a record


Deleting a record


Type in the fields right next to
Select a record by clicking
, hit “delete” key or
.
Change a record

Highlight or click the cell you want to change and type new
information
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Form with AutoForm



Form, easy to enter and update data. With the form, you
can enter or update data on more than one table.
AutoForm allows you to create a form including all fields
of a table.
Open the file “Address”. In the database window, select
the table “tbl_office_info”, click AutoForm
.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Modify the Form




Click on
to switch to Design View
Drag the right border of the form box to the right to enlarge
the size of the form window.
Select the “Mailing Address” box, drag the right border to the
right.
Click on
to switch back to Form View.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Save the Form

Click on
click
, type in the form name and
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Update Records in a Form


In the Design View of the form
Use
to select a record



Change the information by highlighting a field and typing in
new information
To delete a record, select a record and click
Use
to add a new record. The record will
be added to the table simultaneously.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Search for a Record in a Form



In the Design View of the form
Edit-Find, type the last name “Carpenter”, select the table and
“whole field”.
Click
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Form with Form Wizard





Create a form with selected fields from a table.
In the database window, select
Double-click
Select the table you want to create a form from “tbl_home_info”.
Select a field and click
. Click
to select all fields. Click
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Form from Wizard








Select a form layout such as
Click
Select a style such as “Expedition”
Click
Type a form title.
Select “Modify the form’s design”.
Click
Then you are in form design view and you
may modify the form.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Report

Report: Display records in selected fields
 Create
a college phone directory by campus with
following fields:



First Name, Last Name, Title, Extension, Dept., Campus
In the Database Objects Window, select
Double-click on “Create report by using wizard”
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select Fields

Select the table
“tbl_office_info”.



Select fields
Double-click a field one at a time.
Click Next.
•To show the report by
campus
•Select “campus” and
click “>”
•Click Next button.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select Report Layout


Skip the “sort field” step by clicking Next.
Select a layout and click Next
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select a Report Style

Select a style and click
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Title the Report

Type the title of the report, select “modify the report’s
design, click
.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Modify the Report


Go to the Design View of the report by clicking
Resize


Click First Name box, drag the right border to the left to make it smaller.
Move


Click the Last Name title box, hold Shift and click the Last Name field box.
Click the left arrow to move it closer to the First Name field
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Sort the Report by Last Name




Select Score title box, press shift key and select Score
field box.
Click sorting and grouping icon
Select “Last Name” field in the Sorting and Grouping
window. Close the window.
Click
to preview the report.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Make Address Labels



You can create address labels with a Report Table Wizard.
In the Table view, select the table “tbl_home_info”.
Select “Report” wizard icon.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Label Wizard





Select Label Wizard.
Click OK button.
Select a Product
Number for the label
(5160, English, Avery).
“Avery” and “English”
are default. You may
need to change if your
label is not
Avery/English.
Click Next
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select Fonts and Size

Select text font and size (Arial, 10), click “Next”.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Select Fields







Select “First Name” field, click “>” button. Hit the space bar
once.
Select “Last Name” field, click “>” button. Hit “Enter” key.
Select “Address” field, click “>” button. Hit “Enter” key.
Select “Town” field, click “>” button. Hit the space bar once.
Select “State” field, click “>” button. Hit the space bar once.
Select “Zip” field, click “>” button. Hit the “Enter” key.
Click “Next” button.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Sort By
If you would like the labels sorted by last name field, select Last Name
and click “>”.
Click Next button.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Name, Column Width


Give a name for the label
report. Click “Finish” button.
In the next screen, you go to
File, select “Print” to print the
label.
•In the preview window,
click Page Setup, select
Column tab. Change the
column width to 2.5.
•You may need to adjust
column width
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Query Using Wizard

Running a query is to display selected fields with certain
criteria.





In the Database Window, click
Double-click
Select “tbl_office_info” table.
Select fields


People from the LSD division with First Name, Last Name, Title,
Office, Extension, and campus.
Double-clicking a field one by one.
Click Next.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Name the Query



Give a name for the query “qry_LSD”.
Check Modify the Query design.
Click Finish.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Set Up the Criteria and Run the Query



Type “LSD” in the Dept. criteria cell.
Hit Enter key.
Click
to run the query. Click
to save.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create a Query in Design View







Display a phone directory for Business with First Name, Last Name,
Title, Dept, Office, Extension.
In the Objects window, click on
Click
Click Add to add the “tbl_office_info” table. Click Close.
Double-click on a field to select the fields you need in the query.
Type “Business” in the Dept. Criteria row.
Click “!” to run the query. Click the save icon to save the query with a
name.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Show/Hide a Field in a Query



You may hide a field in a query.
In the Query Design view “qry_LSD”
Uncheck the Show box of the Dept field.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Reorder a Query Field

Drag the field and place it in front of
another field.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Sorting in a Table or a Query.

Open a table or a query.
 Click
any cell of the field that you would like to
sort.
 Click the Descending / Ascending icon.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Freeze a Column in Table or Query




You may freeze selected columns in a table if there are many
columns.
Select the columns.
Go to Format, select Freeze Columns.
To turn the Freeze off, go to Format, Select Unfreeze Columns.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Maintain a Database

Compact a
database


Helps run a
database
efficiently and
takes up less
storage space.
In the database
window

Go to Tools,
select Database
Utilities and
Compact and
Repair.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Connections between Tables, Forms,
Reports and Queries



New information entered in a table will be reflected from
a form, a report, or a query generated from the table.
New information entered in a form or a query will be
reflected from the table that generates the form and the
query and a report generated from the table.
Use “Address” file for this practice:



Change office number and the phone extension for a record.
View the “tbl_office_info” table to see the update.
View a query or report to see the update.