Getting Started with Structuring and Relating Data

Today we will be using Microsoft Access to refine our University of the Midwest database.

In Access: The Basics, we began creating a database with two related tables: tblFaculty and tblDepartments. Today, we start with the same database with an additional table: tblCourses. Our major task today is to continue expanding the data structure by creating tables for course sections, section instructors, and meeting days and times and fully incorporating them into our data structure. At the end of today's workshop, there will be an opportunity to practice the skills we will learn by expanding the database to include information about students and their enrollments.

Let's start by taking a look at our database and reviewing some of the relevant concepts from Access: The Basics.

An important note about these materials

Before getting started, it's important to note that these materials were written using the Office 365 version of Access. If you are using a different version of Access to follow along with the materials, such as Access 2016 or older, your interface may look different and certain interface elements may have different names than those shown in these materials. While you'll be able to complete many of the exercises in this course using an older version of Access, some of the steps may refer to interface elements or dialog boxes that have had their names and locations changed. 

Opening the Database

The database we are using today has the same name as the database from the previous workshop. Let's open it now.

Step1. Launch Microsoft Access.

Step2. To begin opening the database, in the navigation on the left side of the screen,

Click Open

Step 3. To browse to a specific location on your computer, under the Other Locations heading,

Click Browse

You see the Open dialog box.

We need to specify the name and location of the file to open.

Setting the Location for Opening Your File

When the dialog box opens, it lists a default location from where the file will be opened. All of our exercise files are contained in the epclass folder, located on the desktop. We'll want to change our location to this folder.

We will start at the desktop, since our exercise file folder, epclass, is located there.

Step1. To move to the desktop,

Click

The current location is now set to the desktop. All of our exercise files are contained in the epclass folder, located on the desktop.

Step2. To open the epclass folder,

Double-Click

The epclass folder contents are now visible.

We will need to open the Access_Structure subfolder of the epclass folder that contains all of the necessary documents.

Step3. To open the correct folder,

Double-Click the Access_Structure folder

Let's open the exercise file now.

Step4. To open the database,

Double-Click UofMW.accdb

The database opens and we are presented with the security warning immediately below the Ribbon.

Step5. To enable content and finish opening the database,

Click

Access may tell you that the file you're trying to open is on a network location and other users may modify the document.

Step6. To confirm that we would like to trust the document, if necessary,

Click

You see the University of the Midwest database.

Reviewing the Database

Before we get started, let's review the structure of the database. To begin, let's go to the Database Tools tab and take a peek at the relationships.

Step1. To review the database relationships,

Click the Database Tools tab, Click

As you can see, the relationships from Access: The Basics are still here:

The tables tblFaculty and tblDepartments are related to each other via the department code field. Let's take a look at the new courses table.

Exploring tblCourses

A new addition to the data from where we finished in Access: The Basics is tblCourses. Let's open it and take a look at the data.

Step1. To open tblCourses, in the Navigation pane,

Double-Click tblCourses

This table contains all of the relevant information about the academic courses offered at the University of the Midwest.

The rules that governed the construction of this table are:

• Each course is required to have a code, title, description, department, and number of credits.
• Courses cannot be worth more than 6 credit hours.
• Course codes are unique across the entire university.

As you can see, the table doesn't currently have a primary key. Any table can have a primary key as an autonumber field; however, in this case, since the previous rules state that each course code must be unique, that field can also serve as our primary key.

Since the course code is a naturally-occurring unique value, let's use it as our primary key.

Step2. To switch to Design view, on the Ribbon,

Click

We are now in Design view.

Step3. To set CourseCode as the primary key,

Click CourseCode, Click

The primary key is now set. Let's take a look at how else we can refine this table.

Refining Table Design

The table is already in pretty good shape, but we could add some validation and set some fields as required to ensure that we are storing high quality data. In the case of tblCourses, we want to only allow users to input a value less than or equal to six for the number of credit hours a course is worth. Validation rules allow us to restrict the values that a user enters based on a particular condition. Let's add the validation rule now.

Step1. To select the CreditHours field,

Click the CreditHours field

With the field selected, we can change the validation rule in the Field properties at the bottom of the screen.

Step2. To edit the validation rule for CreditHours, at the bottom of the screen,

Click Validation Rule, type: <=6

It's a good idea to include some validation text, as well, to explain this business rule, should a user enter an invalid value. The validation text allows us to give more user-friendly instructions to users who enter bad data than Access provides by default.

Step3. To add validation text, on the keyboard, press:

Tab key, type: Courses cannot be worth more than 6 credit hours.

Let's save and test it out.

Step4. Save tblCourses.

When we save, we are warned that our integrity rules have changed and that some of our data may no longer be valid. The dialog box asks us if we would like to check all existing data against the new rules:

We will let Access check our existing data. If we have data that violates the integrity rules, we will see this message:

Since all of our data has been manually curated to meet this new rule, we shouldn't see the second dialog box. Let's have Access check the data against the new rules now.

Step5. To test the data against the new rules,

Click

Since there is no warning after running the test, we know our existing data meets the new requirements. Now that our data is valid, let's test the rule we just created to be sure it meets our needs.

Step6. To switch to Datasheet view, on the Ribbon,

Click

To test the rule, we will try to update the credit hours value for any course to more than six. If our rule works, we will not be able to move to another record until we meet the <=6 condition.

Step7. To test the rule,

Double-Click one of the CreditHours fields, type: 7 Down Arrow key

The down arrow key moved us to the next record, but since we entered bad data, we see a dialog box with our validation text:

Let's revert the record to its previous state.

Step8. To revert the record to its previous state,

Click, press: Esc key

The record reverts to its original value.

Let's also set DeptCode and Description to be required fields.

Step9. Return to Design view.

Step10. To make CourseTitle a required field,

Click the CourseTitle field, Double-Click the Required property

The Required property is now set to Yes. Let's do the same for Description.

Step11. To make Description a required field,

Click the Description field, Double-Click the Required property

The Required property is now set to Yes. Let's do the same for the DeptCode field.

Step12. To make the DeptCode a required field,

Click the DeptCode field, Double-Click the Required property

The Required property is now set to Yes. Let's save and close tblCourses.

Step13. Save tblCourses.

When you save the table, you see the data integrity rules have changed dialog box:

Like before, the data we have created already conforms to the integrity rules. We would like to still test the data against our new required property settings.

Step14. To check our existing data against our new rules,

Click

The data in the table has been checked against the new data integrity rules. We should not see any more dialog boxes.

Step15. Close tblCourses.

You see the Relationships window again.

We're ready to add the table to our relationship scheme, but before we do, let's review the concept of relationships.