Transcript Lecture-8: Hands on Exercise on Microsoft Access Database (Lab-1)

WFM 5201: Data Management and Statistical Analysis © Dr. Akm Saiful Islam
WFM 5201: Data Management and
Statistical Analysis
Lab-1: Microsoft Access Database
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
Objectives
Explore benefits of using a database
 Understand basic Access components

 tables,

queries, forms and reports
Know sources of data at WWU
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Benefits of Using A Database

Data Integrity
 Consistent
entries
 Data validation rules
Ease of data entry with forms
 Minimize duplicate data entry
 Easy Reporting

WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Caution

Designing a database takes time and
technical knowledge
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Access Components
Table
The primary data storage unit in Access. Tables are used
to input and display information. Fields of information are
arranged in columns. There is one row for each record.
Query
Queries gather specific data from one or more tables. The
data can be displayed in a table, form, or report.
Form
Forms are used to input and display information from
tables or queries. They can be arranged for efficient work
and can include special objects, such as drop-down lists
and check boxes.
Report
Reports display information from a table or query. Data
can be grouped and summarized, including calculated
totals and subtotals.
Macros
Automate tasks you perform repeatedly with the need of
programming
Modules
Stores Access Basic Programming Code for custom
program enhancements.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Creating a database

Name the file - Access automatically saves
data as it is entered, so a file name must
be established before working in the
database.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Tables




Tables are the backbone of a database. All the
fields in a table should be closely related
The table design is the arrangement of fields - it
is saved with the save command
A table can have up to 256 fields (columns)
The table data is the information typed into the
fields - it is saved as each entry is completed
a primary key field with a unique value is useful
when establishing relationships between tables.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Creating A Table

On the table tab,
 Click
the New button
 Choose a new table
option
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Table Design




add and delete fields
edit field names
set field properties.
use F1 for help
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Table Fields






Each record (row) has the same fields (columns) with
the same properties
Up to 256 fields (columns) in a table.
Fields contain the smallest amount of information that
you will need: for example, FirstName and LastName
fields instead of a single Name field.
A field name can contain up 64 characters. It is best to
avoid special characters and spaces.
A primary key field is used to help identify records. It
must be a unique value for each record and is useful
when establishing relationships between tables.
The toolbar provides shortcuts for some of the most
common the design tasks.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Access Data Types
Data Type
Text
Memo
Number
Description
Data entries that will not be calculated-including data containing
numbers such as zip codes, phone numbers or order numbers. A
maximum of 255 characters.
Use for comments or notes. Can be up to 32,000 characters (about 8
single-spaced pages).
Numerical data that will (or can) be calculated
Date/Time
Date and time information
Currency
Monetary values
AutoNumber
Yes/No
OLE Object
Hyperlink
Automatically counts entries, incrementing as you enter data. Each
entry will be unique.
Use for Yes/No, True/False and On/Off options
A linked object from an external source
Creates a “clickable” hyperlink
LookUp Wizard Lets you choose a value from another table or from a list of values
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Naming Conventions
Professional database developers use
“naming conventions” to help identify
database objects
tblEmployeeData
qryBudget
frmEmployeeData
rptBudget
or
or
or
or
tEmployeeData
qBudget
fEmployeeData
rBudget
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Importing Data
Data can be imported from many
sources.
Banner, DataW, BI/Query
Excel spreadsheets
If data exists, import instead of re-enter
1.
File, Get External Data
2.
Choose Import or Link
3.
Select the file
4.
Follow the Import Wizard Prompts
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Switching between views
Use the View button to switch between the Design and Datasheet Views.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Working With Tables

When entering data

The pencil shape in the left column
indicates that the current changes have not been
saved.
 New record
Ctrl +
 Current Date Ctrl ;
 Current Time Ctrl :
 Sort Ascending or Descending
 Find Data
 Filter By Selection
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Queries





let you easily repeat sort and filter commands
let you display and/or print only selected
columns
connect one or more tables through similar fields
create new fields based on calculated values
add or remove specific information from tables
with action queries
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Create A Query





Click the Query tab
Click the New button
Choose Design View and select the table(s) or
queries upon which to base the query
Add fields to the “Query by Design” window
Run the query
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Query Criteria
Criteria limits the records returned by the query

Criteria on the same line must all
be met – “AND” criteria

Criteria on different lines will
return records that meet any
criteria – “OR” criteria
In Queries,
OR = More
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Criteria Operators
Operator
Condition
<
less than
>
greater than
=
equal to
<=
less than or equal to
>=
greater than or equal to
<>
not equal
* asterisk
?
Between
all records - usually used with a leading character,
such as B*
a single character wildcard
selects values between two values
Like
must match a pattern - sometimes created by
Access from wildcard input
null
returns blank records
WFM 5201: Data Management and Statistical Analysis © Dr. Akm Saiful Islam
Multiple Table Queries
Join tables on common fields
 One side of the join should be a primary
key (displayed in bold)
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Forms
Fields can be arranged for easy data entry
 Input aids

 Check
boxes
 List boxes
 Combo boxes
 Calculations
 Macros
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Creating A Form

With a table open
 Click
the New Object button
 Choose AutoForm

From the Form tab
 Click
New
 Choose Design View or AutoForm
 Select the table or query that contains the
data for the form
 Click OK
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Navigating with Forms



Tab from field to field
Click the New Record button to add a
record
Use navigation bar to move from record to
record
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Form Design

Click the View button to switch between
Form View and Design View
 Move
Objects
 Resize Objects
 Format Objects
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Reports
Reports are for previewing and printing
only – no data entry
 Reports can group data and perform
calculations
 Reports can be exported to Excel or Word

WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Creating A Report
Click the Report tab
 Click New
 Choose one of the following

 Design
View
 Report Wizard
 AutoReport
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Working with Reports
Report Section
Report Header
Page Header
Description
Prints once at the beginning of the report
Prints at the top of each page (including the first page)
Group Header
Prints at the start of each group
Detail Section
Contains the main body of the report. Prints once for every
record included in the report.
Group Footer
Prints once at the bottom of each group. Sometimes contains
formulas for group totals.
Page Footer
Report Footer
Prints at the bottom of each page.
Prints once at the end of the report. Often used to contain report
totals.
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Sources of Data

Data can be imported from
 Excel
Spreadsheets
 Text files
WFM WFM
5201:6103:
Data Management
and Statistical
Analysis
Dr.Saiful
Akm Saiful
hydrologic Information
System
© Dr. ©
Akm
Islam Islam
Access Version Compatibility
Access 97 can only open Access 97
databases
 Access 2000 can open Access 97
databases and enter data, however they
must be converted to change the database
design
 Access 2002 databases use Access 2000
format by default
