Transcript CIS 250 Advanced Computer Applications

CIS 250
Advanced Computer Applications
Introduction to Access
Basic Database Terms
Field – most basic unit of information in a
database; a characteristic of an entity (ex: last
name, SSN, birth date)
 Record – set of related fields; all of the
information relating to a single entity is
contained in a record
 Table – collection of records; storage unit in a
database, holding information about a group of
entities

University of South Alabama - CIS 250
2
Table Organization
Tables are organized in a spreadsheetlike format
 Columns correspond to fields
 Rows correspond to records

University of South Alabama - CIS 250
3
Example of Poor Data Storage
Employer
ID
10122
10125
10126
10190
10191
10198
10126
10191
10126
Employer Name
BeanTown
Tours
Boston Harbor
Excursions
DaySide Inn &
Country Club
The Briar Rose
Inn
Windsor Alpine
Tours
Trudel Spa &
Resort
Baside Inn &
Country Club
Windsor Alpine
Tours
BaySide Inn
Club
Address
105 State Street, Boston,
MA 02109
75 Atlantic Avenue, Boston,
MA 02110
354 Oceanside Drive,
Brewster, MA 02631
105 Queen Street,
Charlottetown PE CIA 8R4
14 Longmeadow Road,
Laconia, NH 03246
40 Rue Rivard, North Hatley
QC J0B 2C0
354 Oceanside Drive,
Brewster, MA 02631
14 Longmeadow Road,
Laconia, NH 03246
354 Oceanside Drive,
Brewster, MA 02631
Phone
617-4511970
617-2351800
508-2835775
(902) 6361595
603-2669233
8198427783
508-2835775
603-2669233
508-2835775
Position
ID
Position Title
Hours/Week
2045
Tour Guide
24
2082
Reservationist
40
2040
Waiter/Waitress
32
2053
Host/Hostess
24
2078
Ski Patrol
30
2066
Lifeguard
32
2073
Pro Shop Clerk
24
2079
Day Care
35
2111
Kitchen Help
32
redundancy
inconsistent
data
formatting
invalid data
University of South Alabama - CIS 250
4
A Better Solution…
Employer
Table
Employer
ID
Employer Name
10122
BeanTown Tours
10125
Boston Harbor Excursions
10126
BaySide Inn & Country Club
10190
The Briar Rose Inn
10191
Windsor Alpine Tours
10198
Trudel Spa & Resort
Address
105 State Street, Boston,
MA 02109
75 Atlantic Avenue, Boston,
MA 02110
354 Oceanside Drive,
Brewster, MA 02631
105 Queen Street,
Charlottetown PE 03974
14 Longmeadow Road,
Laconia, NH 03246
40 Rue Rivard, North
Hatley QC 09435
Phone
617-451-1970
617-235-1800
508-283-5775
902-636-1595
603-266-9233
819-842-7783
Position
Table
Employer ID
10122
10125
10126
10190
10191
10198
10126
10191
10126
Position
ID
2045
2082
2040
2053
2078
2066
2073
2079
2111
Position Title
Tour Guide
Reservationist
Waiter/Waitress
Host/Hostess
Ski Patrol
Lifeguard
Pro Shop Clerk
Day Care
Kitchen Help
Hours/Week
24
40
32
24
30
32
24
35
32
University of South Alabama - CIS 250
5
Access Objects
Tables – storage unit of the database
 Queries – tool used to select data from one or
more tables based on user-specified criteria
 Forms – created to display information from one
or more tables; used for adding, editing or
viewing; based on table or query
 Reports – used to output meaningful information
from the database

University of South Alabama - CIS 250
6
Additional Access Objects
Data Access Pages – used to display
information from the database in a web page
 Macros – created to automate common
repetitive database tasks such as opening forms
or printing reports
 Modules – created to add additional program
code for specific database functions such as
event procedures to control behavior of forms
and reports and provide responses to user
actions

University of South Alabama - CIS 250
7
Planning Your Database
Define your purpose: information to be stored;
tasks to be accomplished
2. Plan tables and fields: specify what is to be
stored and how data is related; identify and
limit duplicated data
3. Plan queries and reorganize data: see if any
required data was missed; identify any
calculations
1.
University of South Alabama - CIS 250
8
Planning Steps (cont)
Plan forms, pages, and reports: evaluate
meaningful information to be extracted; helps
ensure all required information has been
captured
5. Create the database objects: create tables, add
records and organize them; create forms,
queries, pages, and reports
4.
University of South Alabama - CIS 250
9
Building Your Database
Must create the database before objects
 Two methods

Blank Database – you must specify
everything
 Template – default objects are already
created


Asset Tracking, Contact Management, Inventory,
Expenses, etc.
University of South Alabama - CIS 250
10
Creating Objects - Tables

Next step is to create tables
 Based on database dictionary design from
planning phase
 Tables are designed to hold specific information
 Ex: employee data, student profile information
 Primary key – unique identifier for a record
 Not required, but strongly recommended
University of South Alabama - CIS 250
11
Primary Keys and Foreign Keys

Primary key is created during table design
 If not specified, Access will ask if you wish to
create one when table is saved
 In a relational database, some redundancy is
necessary to establish a relationship or an
association between two tables
 Relationships are established through the use of
foreign keys – a field that corresponds to a field
in another table; not explicit – defined through
relationship
University of South Alabama - CIS 250
12
Creating Tables

Fields are defined based on data dictionary
 Three methods for creating tables:
 Design view – manually specify all fields and
field elements
 Table Wizard – guides you through the
process
 Import data – table is structured based on
data that is imported
 Each field must have a field name and data type
University of South Alabama - CIS 250
13
Data Types

The data type defines what type of information is
to be stored in the field
 Valid data types include text, number, currency,
Yes/No, date/time, memo, autonumber, etc
 Note: calculated is not a valid data type
 Default data type is Text
University of South Alabama - CIS 250
14
Inputting Data

Data may be typed in manually through
Datasheet View
 May create a form to display blank fields for data
input
 AutoForm tool – automatically generates form
based on table structure
 May import data from several formats
University of South Alabama - CIS 250
15
Manipulating Table Data

There are several ways to view and sort data
 From datasheet view, we may wish to display
data in a different order
 Sort allows you to reorder all records:
ascending or descending
 We may only wish to display a subset of records
based on a set of criteria
 Filter by Selection, Filter by Form
University of South Alabama - CIS 250
16
Filter by Selection and Filter by Form

Filter by Selection - Allows you to select all
records containing data matching values that
appear in the same field as the selected field;
based on only one criterion
 Filter by Form – Allows you to select records by
specifying a value in one or more form fields
meeting the specified criteria

You may save filters as queries for later use
University of South Alabama - CIS 250
17
Compact and Repair
Database may become fragmented
 Compact and Repair is used to correct
those problems as well as resize the
database and improve performance
 Tools, Database Utilities, Compact and
Repair

University of South Alabama - CIS 250
18
Lookup Fields
Lookup field – used to access values from a list
or from a field in another table
 Allows
user to pick from available
options instead of manually inputting
information
 Provide data integrity by reducing data entry
errors
 Create through use of Lookup Wizard when
creating a field in your table

University of South Alabama - CIS 250
19
Relationships

A relationship is a logical link between two
tables
 The parent table is the main table in the
relationship
 The child table is the related table
University of South Alabama - CIS 250
20
Types of Relationships



One-to-one – Each record in Table A has only one
matching record in Table B and vice versa.
One-to-many – This is the most common relationship,
where a record in Table A may have multiple records
in Table B. However, a record in Table B can have
only one record in Table A.
Many-to-many – A record in Table A may have many
records in Table B and a record in Table B may have
many matching records in Table C. This is essentially
two one-to-many relationships and is accomplished
through use of a junction table.
University of South Alabama - CIS 250
21
Establishing Relationships
Relationships are established through
Tools, Relationships
 This creates a permanent link between
the tables
 Transient links are often used in queries
to establish a temporary link

University of South Alabama - CIS 250
22
Referential Integrity

Establishing referential integrity enforces that
all child records will have a corresponding
parent record
 When referential integrity is enforced, Access
also ensures that relationships are valid
 Both fields must be of the same data type or
referential integrity cannot be enforced

Note: this does not prevent an invalid relationship from being created
University of South Alabama - CIS 250
23
A more complex example
University of South Alabama - CIS 250
24
Controlling Data Input

To ensure the integrity of the data in the
database, we can control user input
 Input Masks – specifies how data is entered
and displayed
 Validation Rule – specifies requirements for
data entered


Validation Text – used to prompt the user how
the data should be entered to be valid
These may be specified through the field
properties in the table field definition
University of South Alabama - CIS 250
25
Queries





One goal of a database is to allow the user to
extract meaningful information
A query allows you to view, change, and analyze
data based on one or more criteria
Similar to a filter, but more advanced
Can be stored and will display the current data
subset specified upon execution
May display information from one or more tables
University of South Alabama - CIS 250
26
Creating Queries
Design View – user manually specifies
everything
 Query Wizard
 Simple Query Wizard – creates a query
based on questions presented in dialog
boxes. This query option only pulls data
from specific fields in a single table.
 Crosstab Query Wizard – displays data in
a compact, spreadsheet-like format

University of South Alabama - CIS 250
27
Queries (cont)

Queries can be saved and run at any time
 Can be modified and renamed
 Query design options:
 Sort – controls sort order: ascending or
descending
 Show – determine whether a field is displayed
in results
 Criteria – specify criterion record should
satisfy to be displayed (or not displayed)
University of South Alabama - CIS 250
28
Query Criterion




May use relational operators
 <, >, =, <=, >=, <>
 Equal to is default
Logical operators
 AND, NOT, OR
 Nested operators
Other: is, like, in, between, null, is not null
May also use Expression Builder to perform
calculations
University of South Alabama - CIS 250
29
Forms
Used for data input and display
 May contain data in table or query
 Created manually through Design View
 May use Form Wizard to step you
through process
 Additional formatting controls and utilities
may be used to ensure data integrity

University of South Alabama - CIS 250
30
Controls





All information and objects on a form are contained in
controls
Display data, perform actions, or aesthetics
Bound controls – associated with a particular field in
a table (e.g. text box displaying a last name)
Unbound controls – not linked to a field in a table
(e.g. a picture, line around a group of text boxes, label)
Calculated controls – use an expression as the
source of data
University of South Alabama - CIS 250
31
List Boxes, Combo Boxes, and DropDown List Boxes
List boxes – used to display a list at all times
 Limited to set of alternatives in list
 Combo box – list box that is not displayed until
opened by user; uses less room on form; user
may specify additional data values
 Drop-down list box – same as combo box, but
user is limited to only the set of alternatives in
the list.
 Created by specifying Limit to List option

University of South Alabama - CIS 250
32
Additional Formatting

Conditional Formatting – control output
based on criteria

Used to draw attention to field contents (e.g.
displaying all amounts due over $250 in red)
University of South Alabama - CIS 250
33
Form Sections
Form Headers and Footers may be used
to display titles, graphics, or other items
at top/bottom of form page
 Background colors and other common
elements may also be modified
 Detail section contains data stored in
table records

University of South Alabama - CIS 250
34
Subforms
Used to show information from related
tables
 Requires relationship to already be
established
 May use SubForm Wizard to create a
subform control
 Useful when displaying detail records
relating to a parent record (e.g. expense
detail record, employee timecard record)

University of South Alabama - CIS 250
35
Creating and Modifying Reports
Create manually through Design View
 Form Wizard – prompts for table/field
info, grouping levels, layout, etc.
 If using a query, query must be created
first
 Just as with Form controls, report
controls are either bound, unbound, or
calculated

University of South Alabama - CIS 250
36
Parts of Report Layout
Report Header Band – only displayed on first
page; displays the report name
 Page Header – info to be displayed on each
page of the report; titles of columns and other
important information is included in this
section
 Group Header Band – info to be displayed for
each group such as a group title
 Detail band – data from records; info from the
table or query

University of South Alabama - CIS 250
37
Parts of Report Layout (cont)

Group Footer band - text and subtotals for a
group of records
 Page Footer – text and data to be placed at
bottom of each page such as a date and page
number
 Report Footer – text and data printed at the
bottom of the last page in a report; summary
info for all groups may be entered in this
section
University of South Alabama - CIS 250
38
Additional Form Controls

May create buttons to allow user to
perform operations without requiring them
to use the toolbars
 Command buttons to be created: Add a
Record, Modify (Save) a Record, Find a
Record, Exit (Close) Form
 Use the Command Button Wizard to
create
 Remember to give your button a
meaningful name
University of South Alabama - CIS 250
39
Menus
Menus are basically forms created to
allow a user to access your data entry
forms and print reports without having to
use the toolbar to access those objects
 Create a blank form, add a title, and
create buttons to open each data entry
form and to exit the database
 Again, remember to give your buttons
meaningful names

University of South Alabama - CIS 250
40
CIS 250
Advanced Computer Applications
Introduction to Access