Transcript Mgt 240 Lecture

Mgt 240 Lecture
MS Excel and Access:
Introduction to Databases
September 23, 2004
Miscellaneous





Homework 3 grades posted Friday or Saturday
Homework 4 due next Friday, 10/1/04, by 5pm
Posted grade spreadsheets and scrolling to see
comments
Grading questions – review question in
homework before asking what you did wrong!
Please use a different name for your turned in
homework than the original homework name –
that way you’re more likely to turn in the right
file
Today’s Objectives

Introduction to Databases


Excel database capabilities
Relational databases

Access
Database Definition


An integrated collection of data that
is organized to meet the
informational needs of multiple users
in an organization
Database examples
The Traditional Approach To Data
Management
The Database Approach to Data
Management
The Hierarchy of Data
Field, Record, Table
Field - a single characteristic or attribute of a person, place,
object, event or idea (column)
Record - a collection of field values (row)
Table - a collection of records
Keys and Attributes
Excel Databases
Using Excel to Create Databases





Databases are called “Lists” in Excel
Excel provides features that allow you to maintain lists of
information such as customer lists, telephone lists,
inventory lists, and so on.
An Excel list is a collection of rows and columns that
contain similar data.
In a worksheet, each column represents a field of data
and each row represents a record of data.
The first row of the list always contains the name of the
fields and is called a field header row.
An example of an Excel list
Gourmet.xls
Freeze rows and columns



When you scroll through large amounts of data in a
worksheet, you can move data off the screen.
If you prefer to have portions of data remain on the
screen at all times, such as the column and/or row
headings, you can freeze a portion of the list so that it
remains while the rest of the data scrolls.
To freeze rows and columns:
 Click in a cell to select it
 Click Window on the menu bar, and then click Freeze
Panes to freeze the rows above the selected cell, and
the columns to the left of the selected cell
 Excel will display dark vertical and horizontal lines to
indicate the rows and columns that are frozen
A frozen datasheet

Gourmet.xls
Find and replace values in a
worksheet



The Find command allows you to search
through the data in a worksheet for a
particular character string.
Optionally, you can choose to replace the
character string with another string.
This procedure is called Find and Replace.

For example, you might want to find every
occurrence of ACCT and replace it with
Accounting
The Find and Replace dialog box
Gourmet.xls
Sort data in a list



Excel makes it easy to sort a list in
ascending or descending order based on
any field(s) in the list.
The field(s) selected on which to sort are
called the sort fields or the sort keys.
You may choose to sort the data on a
single field or on a collection of fields.
Sort using a single sort key
Gourmet.xls
Sort using multiple keys
Gourmet.xls
Use a data form to enter, search
for, edit, and delete records



Sometimes it is easier to view the data in
a list through a data form.
A data form is a dialog box that you can
use to arrange data to view one record at
a time.
You can use the data form to display
records, to search for records, to modify
records, and to delete records from the
Excel list.
An Excel data form
Gourmet.xls
Filter data in a list using
AutoFilters



Sometimes you will want to see a portion of the
records instead of all of them.
The process of displaying only those records
that meet some criteria is called Filtering.
When data in the list is filtered, records that do
not meet your criteria are hidden.



These records are not removed from the list and,
therefore, can be redisplayed by removing the filter
Simple filters can be specified by clicking the list
arrow on any field name cell.
More complex filters must be created using the
Custom AutoFilters option.
AutoFilter options
Gourmet.xls
Apply conditional formatting
to a range



There are times when you will want data to have a
different appearance if it meets some criteria.
 For example, you might want data to appear in red, if
the data is more than six months old
 Or, you might want a value to be black if it is positive
and red if it is negative
This kind of formatting is called conditional formatting.
You specify the condition under which you want the
formatting to take place and what the formatting should
be.
The Conditional Formatting
dialog box
Gourmet.xls
Insert subtotals into a list




The data in a list can be summarized by adding subtotals
to the list.
You can include summary information such as a count, a
sum, an average, a minimum value, and or a maximum
value.
When the Subtotals command is applied to the list, a
subtotal row is automatically added to the list.
You can specify that you want the subtotal(s) to apply to
the worksheet and/or groups within the worksheet.
The Subtotal dialog box
Gourmet.xls
An Excel datasheet with totals
added
Use the subtotals outline view




Previously you learned about creating
subtotal lines within a worksheet.
Sometimes, it might be more beneficial to
view the summary information only.
You can do this by displaying the data in
Subtotals Outline View.
You may choose from Level 1, Level 2,
and Level 3 outline view.
Totals displayed in Outline view
Gourmet.xls
When to Use Excel for a Database

Use Excel when you:
 Require a flat or non-relational view of your
data (you do not need a relational database
with multiple tables).



This is especially true if that data is mostly
numeric—for example, if you want to maintain a
financial budget for a given year.
Want to run primarily calculations and
statistical comparisons on your data—for
example, if you want to show a cost/benefit
analysis in your company's budget.
Know your dataset is manageable in size
(no more than 15,000 rows).
Access Databases
Data Redundancy
A problem that occurs when a database is set up inefficiently.
Leads to data entry errors.
This example illustrates a “flat file” (like an Excel list) with redundant data.
This table would be better divided into two tables containing “like” data -Customer Information and Order Information.
This process is called Data Normalization.
Gourmet.xls
Normalizing Gourmet.xls


Reduce redundancy in data
Logically organize data into separate, distinct entities



Separate objects, people, events
Specify relationships between entities
Gourmet.xls

How many separate entities can you identify in this file?
Fields in Entities in Gourmet.xls

Customer


CompanyName
Country
Relationships between Entities in
Gourmet.xls
Customer
Company Name
Country
Sales Rep
SalesRep
Product
Order
OrderDate
Quantity
Discount
Freight
Product Name
Category
UnitPrice
Shipper
Shipper
Primary Keys in Entities in Gourmet.xls
Customer
Company Name *
Country
Sales Rep
SalesRep *
Product
Order
OrderDate
Quantity
Discount
Freight
Product Name *
Category
UnitPrice
Shipper
Shipper *
Foreign Keys in Entities in Gourmet.xls
Combined
Primary Key
Customer
Company Name *
Sales Rep **
Country
Sales Rep
SalesRep *
Product
Order
Product Name **
Company Name **
OrderDate *
Quantity
Discount
Freight
Shipper **
Product Name *
Category
UnitPrice
Shipper
Shipper *
Primary & Foreign Keys
Primary Key
Field:
A field whose
value uniquely
identifies each
record in the
table
Foreign Key
Field:
A primary key
field from one
table placed in a
second table to
form a
relationship
between the
tables
Relational database and keys


A relational database is a collection of
tables that are related to one another
based on a common field.
A field, or a collection of fields, is
designated as the primary key.


The primary key uniquely identifies a record in
the table.
When the primary key of one table is
represented in a second table to form a
relationship, it is called a foreign key.
Relational Database Model
Creating a Relational Database in
Access


Create tables in design view
Specify fields



Make sure to include foreign keys
Specify primary key
Gourmet.mdb
What is an Access query?




If you want to see just a portion of the data in a
table or tables you can create a query.
A query is a question you ask about the data
stored in a database table or tables.
Access responds by displaying the data
according to your question.
 For example, if you ask to see all the
customers from New York, the response
would be to display only the records whose
state field matches with NY
Gourmet.mdb
Linking Database Tables
to Answer a Query
When to Use Access for a Database








Require a relational database (multiple tables) to store your data.
May need to add more tables in the future to an originally flat or
non-relational data set.
Have a very large amount of data (thousands of entries).
Have data that is mostly of the long text string type (not
numbers or defined as numbers).
Rely on multiple external databases to derive and analyze the
data you need.
Need to maintain constant connectivity to a large external
database such as one built with Microsoft SQL Server.
Want to run complex queries.
Have many people working in the database and want robust
options to expose that data for updating.
More Access Resources from
Microsoft






Using Access or Excel to manage your data
Sample Access databases that you can
download and adapt
About Access databases
About designing a database
Description of the database normalization
basics
Roadmap to Access 2003 training
Assigned Reading


Work through Tutorial 5 (pp. 195-225) in Excel 2003 text
Following are the tasks you should be able to perform using Excel’s
list features:











Freeze rows and columns
Use find and replace
Create a list range
Sort data
Maintain a list
Use a data forms
Filter a list
Use conditional formatting
Use the total row to calculate totals in a list
Insert subtotals into a list
Use the subtotals outline view