Exploring the Database

Today we will be working with a database that contains information for managing student enrollments at the University of the Midwest.

Our database holds accurate and timely information about the departments, faculty, courses, students, and their enrollments. This information is of little value to us unless we are able to analyze the information and answer questions about it. To examine just the information that we want and to relate it in a meaningful manner, we rely on the use of queries within Access.

We will query the University of the Midwest database to retrieve customized data sets and summarized data sets. We need to be able to see things like which classes students are taking, who is teaching the classes, and where classes are being held. Access queries will help us to sort, filter, summarize, analyze, update, append, and delete the information in our tables.

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. 

Let's look at the University of the Midwest database we will use in today's workshop. We will launch Access and then open and explore the database.

Step1. Launch Access.

Step2. To begin opening an existing database, in the left pane,

Click Open 

Step3. To browse to a location on the computer, under the Other Locations heading,

Click

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_AMD subfolder of the epclass folder that contains all of the necessary documents.

Step3. To open the correct folder,

Double-Click the Access_AMD folder

Step4. To open the database file,

Double-Click UofMW.accdb

Step5. To enable the content,

Click

NOTE: Once a file has been opened and enabled from a specific location, Access will consider this location a trusted source and will not show this warning again. For more information regarding trusted documents, refer to the Access online Help documentation.

If you are opening this database from an untrusted network location, you will see the following dialog box:

You can choose to make this document trusted, which will mean that Access will not warn you about enabling content every time you open the database. In general, you should only make documents trusted if they have come from someone you trust, and you were expecting the file. Otherwise, click No.

Step6. To make the file a trusted document, if necessary,

Click

Now that we have our database open in Access, we will review the tables and relationships.

Viewing Tables

There are twelve tables in the University of the Midwest database: tblCourses, tblDepartments, tblEnrollments, tblFaculty, tblGradeScale, tblLocations, tblMeetingTimes, tblMeetingDays, tblRoles, tblSections, tblSectionsInstructors, and tblStudents. Let's take a moment to look at some of these tables so we have a clear idea of what information they contain. In particular, we will look at the tables we added between Access: Structuring & Relating Data and today's workshop.

Step1. To view the Students table, in the Navigation pane,

Double-Click tblStudents

The Students table collects information about the students including student_id, first_name, last_name, email, phone, address, academic program, and enrollment date.

Access automatically creates subdatasheets when the table is in a one-to-one relationship or the table is on the "one" side of the relationship and the table's SubdatasheetName property is set to Auto.

Step2. To view a subdatasheet, next to any record,

Click

The subdatasheet is displayed for the record:

Each enrollment from tblEnrollments for that student is displayed. If the student has no enrollments, you still see the headings for all the columns in tblEnrollments.

NOTE: If a table is on the "one" side of multiple relationships, instead of seeing a subdatasheet, the Insert SubDataSheet dialog will open. This dialog box allows you to pick what data you want to see in the subdatasheet. To learn more about subdatasheets, visit this Microsoft support page about how to add a subdatasheet.

Step3. To open the Enrollments table,

Double-Click tblEnrollments

This table shows the enrollments and grades for every course a student is enrolled in. Notice that the student enroll in a section of a course rather than directly in a course, since a larger course can have many different sections in which it is offered.

Step4. To open the Grade Scale table,

Double-Click tblGradeScale

This table is used to translate the letter grades in enrollments into point values. We will use this later when calculating GPAs for students.

Step5. To open the Locations table,

Double-Click tblLocations

This table contains information about locations on campus and includes information like room number, capacity, type of room, and building information. You might notice that the building information is repetitive and thus not normalized. We will fix this in a later exercise where we will split the building information off into its own table.

Step6. To open the Meeting Days table,

Double-Click tblMeetingDays

This table contains the days when classes meet.

Step7. To open the Meeting Times table,

Double-Click tblMeetingTimes

This table contains the times when classes meet.

Step8. To close all the tables, at the top of the object workspace,

Right-Click any tab, Click Close All

The tables are closed.

Viewing Relationships

The structure of the University of the Midwest database includes tables that allow us to track information about the classes offered at the University of the Midwest. Because all of these table are related, we can ask Access to display in one datasheet information about what department a class is in, who is teaching the class, where the class is located and which students are enrolled in the class.

When working with a relational database, it is important to understand the relationships that exist between the tables. Let's open the Relationships window to view how the tables are related.

Step1. To see the available database tools, on the Ribbon,

Click the Database Tools tab

Step2. To view the table relationships for the database, in the Relationships group,

Click

The Relationships window appears for the UofMW.accdb database. Note the symbols indicating one-to-many relationships with referential integrity established. All our relationships are one-to-many relationships. The following table summarizes the relationships shown in the Relationships window:

Step3. To close the Relationships window, at the top of the object workspace,

Right-Click the Relationships tab, Click Close

As we have seen in the previous workshops in this series, queries are questions we ask about data. In a relational database, the information necessary for answering a question can be located in different tables. Since relationships have been established between tables, we can write queries that return information from one or more tables.