CS230 Lecture 17

Introduction to Access

Tuesday, April 20, 2004

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

Customer Number	Name	Address	City	State	Zip Code	Amount Paid	Current Due	Driver Number
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

Open up Microsoft Access and select create blank database. Save the database on your folder on (Winter). 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.

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.

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.

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.

Enter the data in the datasheet view.

Once you have completed this table, create a new table, in a similar way, to store the driver information. The primary key will be the driver number.

Driver Number	Last Name	First Name	Address	City	State	Zip Code	Hourly Rate	YTD Earnings
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.

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

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:

Query 1: For the customer DB, find and print all customers in Hansen, but print only the Customer Number, Name, and Amount Paid.

Query 2: For the same field information as Query 1, print all customers that have a current due bill over $1,000.

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.

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

To join tables in Access, first you bring field lists for both tables in the upper pane of the select query window.

Problem 3

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

Select create query and when it asks you which tables you wish to use, select both the customer and driver tables. You will see that a line is drawn between the two tables. The line indicates the driver number, which is a common field in both tables.

Problem 4

OK, letπs see if you can use 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 5

Again using the help feature, add Average, Maximum, and Minimum statistics for the Amount Paid and Current Due.

---