CIS 250Advanced Computer Applications

Introduction to Access

University of South Alabama - CIS 250 2

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 3

Table Organization

Tables are organized in a spreadsheet-like format

Columns correspond to fieldsRows correspond to records

University of South Alabama - CIS 250 4

Example of Poor Data Storage

Employer ID Employer Name Address Phone

Position ID Position Title Hours/Week

10122 BeanTown Tours

105 State Street, Boston, MA 02109

617-451-1970 2045 Tour Guide 24

10125 Boston Harbor Excursions

75 Atlantic Avenue, Boston, MA 02110

617-235-1800 2082 Reservationist 40

10126 DaySide Inn & Country Club

354 Oceanside Drive, Brewster, MA 02631

508-283-5775 2040 Waiter/Waitress 32

10190 The Briar Rose Inn

105 Queen Street, Charlottetown PE CIA 8R4

(902) 636-1595 2053 Host/Hostess 24

10191 Windsor Alpine Tours

14 Longmeadow Road, Laconia, NH 03246

603-266-9233 2078 Ski Patrol 30

10198 Trudel Spa & Resort

40 Rue Rivard, North Hatley QC J0B 2C0 8198427783 2066 Lifeguard 32

10126 Baside Inn & Country Club

354 Oceanside Drive, Brewster, MA 02631

508-283-5775 2073 Pro Shop Clerk 24

10191 Windsor Alpine Tours

14 Longmeadow Road, Laconia, NH 03246

603-266-9233 2079 Day Care 35

10126 BaySide Inn Club

354 Oceanside Drive, Brewster, MA 02631

508-283-5775 2111 Kitchen Help 32

redundancy

inconsistent data

formatting invalid data

University of South Alabama - CIS 250 5

A Better Solution…Employer Table Employer ID Employer Name Address Phone

10122 BeanTown Tours 105 State Street, Boston, MA 02109 617-451-1970

10125 Boston Harbor Excursions 75 Atlantic Avenue, Boston, MA 02110 617-235-1800

10126 BaySide Inn & Country Club 354 Oceanside Drive, Brewster, MA 02631 508-283-5775

10190 The Briar Rose Inn 105 Queen Street, Charlottetown PE 03974 902-636-1595

10191 Windsor Alpine Tours 14 Longmeadow Road, Laconia, NH 03246 603-266-9233

10198 Trudel Spa & Resort 40 Rue Rivard, North Hatley QC 09435 819-842-7783

Position Table

Employer ID Position ID Position Title Hours/Week

10122 2045 Tour Guide 24 10125 2082 Reservationist 40 10126 2040 Waiter/Waitress 32 10190 2053 Host/Hostess 24 10191 2078 Ski Patrol 30 10198 2066 Lifeguard 32 10126 2073 Pro Shop Clerk 24 10191 2079 Day Care 35 10126 2111 Kitchen Help 32

University of South Alabama - CIS 250 6

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 7

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 8

Planning Your Database

1. 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

University of South Alabama - CIS 250 9

Planning Steps (cont)

4. 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

University of South Alabama - CIS 250 10

Building Your Database

Must create the database before objectsTwo 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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

Compact and Repair

Database may become fragmentedCompact 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 19

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 20

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 21

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 22

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 23

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 24

A more complex example

University of South Alabama - CIS 250 25

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 enteredValidation 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 26

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 27

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 28

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 29

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 30

Forms

Used for data input and displayMay contain data in table or queryCreated manually through Design ViewMay use Form Wizard to step you

through processAdditional formatting controls and utilities

may be used to ensure data integrity

University of South Alabama - CIS 250 31

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 32

List Boxes, Combo Boxes, and Drop-Down 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 33

Additional Formatting

Conditional Formatting – control output based on criteriaUsed to draw attention to field contents (e.g.

displaying all amounts due over $250 in red)

University of South Alabama - CIS 250 34

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 35

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 36

Creating and Modifying Reports

Create manually through Design ViewForm Wizard – prompts for table/field

info, grouping levels, layout, etc.If using a query, query must be created

firstJust as with Form controls, report

controls are either bound, unbound, or calculated

University of South Alabama - CIS 250 37

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 38

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 39

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 createRemember to give your button a

meaningful name

University of South Alabama - CIS 250 40

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

CIS 250Advanced Computer Applications

Introduction to Access