CS230

Introduction to Access

What is Microsoft Access?

Microsoft Access is a powerful database management system (DBMS). Some of the key features are:

á      Data entry and update: add, change or delete data in mass

á      Queries: ask complex questions about the data

á      Forms: produce attractive and useful forms for viewing and updating the data

á      Reports: create sophisticated reports for printing data

á      Web support: allows you to save objects in HTML

 

People keep records (such as phone numbers, names, birthdays, records of expenses, etc). To get the most out of these records, they should arranged for quick access.

 

The word database describes a collection of data organized in a manner that allows easy access, retrieval and use of the data. The data should also be linked if there is any relationship between them.

 

DBMS refers to a piece of software that allows you to create, change, add and delete data in a database.

 

A database consists of a collection of tables, where the rows in the tables are called records and the columns in the tables are called fields.

Creating Tables

LetÕs jump in and explore Access.

Problem 1

The example that we are going to work with today is Alisa Vending Services, which is a company that places vending machines at its customerÕs facilities. Each customer gets a share of the profits of the machine, which is paid quarterly. Alisa much track the amount that has been paid as well as the amount due to each customer.

 

Alisa also employs drivers to deliver and service the vending machines. Each customer is assigned a specific driver.

 

Below is a table containing the data on the customers and the drivers.

 

CustomerNumber

Name

Address

City

State

ZipCode

AmountPaid

CurrentDue

DriverNumber

BA95

Bayside Hotel

287 Riley

Hansen

FL

38513

$21,876.00

$892.50

30

BR46

Baldwin-Reed

267 Howerd

Fernwood

FL

37023

$26,512.00

$2,672.00

60

CN21

Century North

1562 Butler

Hansen

FL

38513

$8,725.00

$0.00

60

FR28

Friend's Movies

871 Adams

Westport

FL

37070

$4,256.00

$1,202.00

75

GN62

Grand Nelson

7821 Oak

Wood Key

FL

36828

$8,287.50

$925.50

30

GS29

Great Screens

572 Lee

Hansen

FL

38513

$21,625.00

$0.00

60

LM22

Lenger Mason

274 Johnson

Westport

FL

37070

$0.00

$0.00

60

ME93

Merks College

561 Fairhill

Bayville

FL

38734

$24,761.00

$1,572.00

30

RI78

Riter University

26 Grove

Fernwood

FL

37023

$11,682.25

$2,827.50

75

TU20

Turner Hotel

8672 Quincy

Palmview

FL

36114

$8,521.50

$0.00

60

 

  1. Open up Microsoft Access and select create blank database. Save the database as Alisa on the desktop for now and then on Turing before you leave. Double click on the option Create Table in Design View. Here you need to specify the name and data type for each field in the table.
  2. You will need to specify a primary key for each table in your database. The primary key refers to a record that is unique in the table. For this example, we want to set Customer Number to be the primary key as this is unique to each customer. You set the primary key by right clicking on the box to the left of the record and selecting primary key.
  3. When you are done with creating all fields, then close the table. Be careful to just close the table and not the whole database. Call the table Customer.
  4. You will now see the table listed in the main database window. Double click on this to start entering the data in the table. You can move between the different views of the table (design and datasheet views) by going to the view menu and selecting the required view.
  5. Enter the data in the datasheet view.
  6. Once you have completed this table, create a new table, in a similar way, to store the driver information. Call the table Driver. The primary key will be the driver number.

 

DriverNumber

LastName

FirstName

Address

City

State

ZipCode

HourlyRate

YTDEarnings

30

Tuttle

Larissa

7562 Hickory

Laton Springs

FL

37891

$16.00

$21,145.25

60

Powers

Frank

57 Ravenwood

Gillmore

FL

37572

$15.00

$19,893.50

75

Ortiz

Jose

341 Pierce

Douglas

FL

37613

$17.00

$23,417.00

Database Design

Database design refers to the arrangement of data into tables and fields. One needs to design to remove redundancy. Redundancy:

á      Wastes space on disk

á      Makes updating more complicated

á      Allows for inconsistent data to occur

Database Queries

A query is a question represented in a way that Access can understand.

Problem 2

Going back to our Alisa Vending Company, let us create some queries.

 

  1. Using the customer table, select queries and then do a new query using the design view option. Double click on the customer number, name and driver number then select run query (an ! on the toolbar).
  2. You can limit your queries by using the criteria option. Going back to the design view (from the view menu) type in FR28 as the criteria for the customer number.

 

You can also use wildcard characters:

á      * represents any collection of characters, B* represents any text that starts with B

á      ? represents any single character, t?m represent the letter t followed by any single character followed by m

 

Now itÕs your turn.

 

Create the following three queries. Give these queries appropriate names:

 

  1. Query 1: For the customer DB, find and print all customers in Hansen, but print only the Customer Number, Name, and Amount Paid.
  2. Query 2: For the same field information as Query 1, print all customers that have a current due bill over $1,000.
  3. Query 3: For the same field information as Query 1, print all customers that have an amount paid over $10,000 and driver number 60.

Joining Tables

If a query cannot be satisfied by using one table, then we need to join tables.

 

  1. Question: Can you think of a query that cannot be satisfied by the data in one table?

 

 Problem 3

  1. Using Access help, join the two tables in the existing database.

Problem 4

 

  1. Create a query to list the name of each customer along with the name of the customerÕs driver.

 

Problem 5

 

  1. OK, continue using the Help Facility with Access and perform the following Query. The total amount for each customer is the amount paid ÒplusÓ the current due. Search for the phrase Òcalculated fieldÓ in the help facility and create a Query that produces the Customer Number, Name, and Total Amount (which is the sum of the Amount Paid plus Current Due fields).

Calculating Statistics

Access supports the built-in statistics:

á      COUNT

á      SUM

á      AVG

á      MAX

á      MIN

á      STDEV

 

These functions are called Òaggregate functionsÓ which perform some mathematical function against a group of records.

 

Problem 6

  1. Again using the help feature, create a query that shows the Average, Maximum, and Minimum statistics for the Amount Paid and Current Due.
  2. Export the Customer table to an Excel file and check your results.