Home Dave Heppenstall | E-mail Contact

Find 
 
[Résumé]Résumé [Academics]Academics [About Me]About Me
Lab 3 4 5 6 7 8 9 10 |  » 

Access II

28 February 2008

This tuitorial is an update to the official Lab Seven web page. It is also an update to the official supplemental Access Lab Notes and Database Menus pages.

We'll need a file for today's lab; open the Course FTP Server (Access Labs folder) and download the student.accdb file to your USB drive.

On This Page:

Relationships

Start Microsoft Access and open the database we downloaded. If you see the following security warning, click the Options button, select Enable this Content and click "OK":

Security Warning

Take a look at the three tables contained within this database (student, transactions and BOOK). You will notice that there are some common fields used; both the BOOK and transactions table contain a Book Number field and both the student and transactions table contain an ID field:

Common Fields

To make our database more cohesive, it would be a good idea to connect these fields using "Relationships." Open the Database Tools tab menu and click on Relationships as follows:

Relationships

In the new Relationships window which appears, click on the table entries in the Tables pane on the left side of the screen and drag them into the window. The tables will appear in the window showing an index of the fields which they contain.

To relate these fields, click on a common field name (ID or Book Number) in one table and drag it to the akin field in the other table. An Edit Relationships window will appear. Check that the fields match and click "OK":

Editing Relationships

Once you are finished, the Relations window will show you your new Relational Database with fields in different tables connected (related) to each other.

Relational Database

Multiple Table Queries

By relating the common fields of the tables, we are now able to perform queries which span the entire database. Start by creating a new query as demonstrated in Lab Six (Running Queries).

Add each of the three tables to the query and add the following fields: Name from Student, Title from BOOK and date from transactions. Additionally, select "Ascending" in the "Sort" row under Name:

Multi-Table Query

Without entering anything in the Criteria row, the result of the query will display all of the records which contain matching entries across the tables:

Multi-Table Query Result

In this case, we are viewing information about books which have been checked out by students and the date of the transaction.

Building Expressions

We're going to add some additional fields to our criteria.

First, add the Cost field from the transactions table. Next, we're going to create our own specialized field to show what the cost of replacing the book would be if the student lost it. The replacement cost is defined by the library as 75% of the original cost.

Click on the empty column to the right of the Cost column we just added. Click on the Design tab menu and click Builder as follows:

Expression Builder

The Expression Builder window appears. Expand the Tables folder and select BOOK - the fields of this table will appear in the middle box. Double click on Cost to add this field to the expression. Add "*0.75" after the field as follows and click "OK."

Expression Builder

When you run the query, you will see the two new columns with one being 75% of the other. Save this query and call it "ReplacementCost".

Crosstab Query Wizard

A Crosstab Query is a simple and compact spreadsheet-style view for tables and queries. To open the Wizard, click on the Create tab menu and select Query Wizard:

Query Wizard

In the Wizard, select Crosstab Query Wizard and click "OK." Choose the Query, "ReplacementCost," which we created earlier; click next. Choose Name as the row headings; next. Choose Title as the column headings; next. Finally, display the result of "Expr1" (the replacement book cost) for each cell; specify including the row sums and click next.

Query Wizard Query Wizard
Query Wizard Query Wizard

Choose to "View the query" and click Finish. The "Total of Expr1" column will display the amount each student would need to pay if they loose all the books currently checked out:

Query Wizard Result

Lab 3 4 5 6 7 8 9 10 |  » 

- Top of Page