Creating Relational Queries

A relational query is a query that uses multiple related tables as its data source. Let's explore some properties of relational queries. Suppose we would like to see all courses being taught at the university during summer 2018, including their CourseCode, SectionNumber, SemesterYear, and CourseTitle. We would also like to be able to add new section records via the query.

Let's create the query now.

Step1. To begin building a query, on the Ribbon,

Click the Create tab, Click

The query designer opens. For our query, we need to see tblCourses and tblSections. Let's add them now.

Step2. To add tblSections and tblCourses, in the Add Tables pane,

Double-Click tblSections, Double-Click tblCourses

The tables appear in the query designer:

NOTE: If desired, you can resize the tables at this point to see all of each table's fields.

Selecting Fields for a Query

When we are building a query, it's important to keep in mind the primary purpose the query will serve in our database. The primary purpose of this query, aside from viewing data about the courses and sections, is to be able to easily add sections of any course in our course catalog. You will notice that the CourseCode field is a join field, a field that creates a relationship between two tables. The important question is: How do we know which of these key fields to use in the query?

To answer the question, let's examine the way Access will behave depending on which field is selected. First, if we choose the primary key field, CourseCode from tblCourses, from the query, we could add new courses to tblCourses. This is not the behavior we are looking for in this case. We can create courses by adding a record directly to tblCourses.

If we were to select the foreign key field, CourseCode from tblSections, Access will be able to look up data from tblCourses based on the one-to-many relationship we've already established. This can be a real time saver in data entry tasks. Just remember that lookup fields are always created in the foreign key field, which then allows Access to look up other information from the primary table based on this value. Selecting the foreign key field also allows us to add new sections for a particular course from the query.

Let's add the fields we need to see to make this query work.

Step1. To add the fields to the query, from tblSections,

Double-Click SemesterYear, Double-Click CourseCode, Double-Click SectionNumber

The fields are added to the query designer. Now let's add the CourseTitle field from tblCourses.

Step2. To add the CourseTitle field from tblCourses,

Double-Click CourseTitle

Let's view the results.

Step3. To view the query results, on the Ribbon,

Click

This query contains many records and isn't quite exactly what we were looking for. Let's adjust the column widths to allow us to see more of the data in each column.

Step4. Resize the columns as you see fit in order to see the data more clearly.

Let's limit the scope of our query to only show courses being offered during the summer.

Step5. Switch back to Design view.

Step6. To filter to courses offered during summer 2018, in the Criteria field for SemesterYear, at the bottom of the screen, type:

SU18 Enter key

Since we know that the query will only return summer 2018 courses, let's hide this column.

Step7. To hide the SemesterYear column,

Click

Let's save and view the query.

Step8. To save the query, press:

Control key + S

The Save Query dialog box appears.

Step9. To name the query, type:

qryCoursesAndSectionsSummer18, press: Enter key

The query is now saved.

Step10. To view the query, on the Ribbon,

Click

This returns a more manageable set of 49 records that correspond to all the sections being offered during summer 2018.

Adding More Tables to the Query

Let's make one more modification to this query. Let's add in the instructor of the section.

Since we're changing the query, we will give it a new name.

Step1. Return to Design view.

We can use the keyboard shortcut F12 key to invoke the Save As command from the File tab. We will also be saving our query with a new name before making any sort of modifications. This way, if we accidentally save with Control key + S, we won't be overwriting an already-created, useful query.

Step2. To save the query with a new name, press:

F12 key, type: qryFacultyCoursesSectionsSummer18, press: Enter key

We need to add tblFaculty to the query so we can get the instructor's first and last names.

Step3. To add tblFaculty to the query, in the Add Tables pane,

Double-Click tblFaculty

NOTE: You can Press & Drag tblFaculty from the Navigation pane into the query designer, too.

Step4. Adjust the tables in the query designer as you see fit.

You see tblFaculty, tblSections, and tblCourses:

NOTE: You may need to rearrange the tables to see them in this order.

We want the instructor's first and last name to be added to the query.

Step5. To add the first and last name of the instructor, in tblFaculty,

Double-Click LastName, Double-Click FirstName

The fields are added to the query designer.

Step6. Reorder the fields however you see fit.

Let's view the query results.

Step7. To view the query, on the Ribbon,

Click

We see the same 49 records but now we also see the instructor's last name and first name.

Step8. Save qryFacultyCoursesSectionsSummer18.

Our query is working well, but it only lists those faculty who are teaching courses in the summer. How can we see all faculty members and the courses they're teaching, if they are teaching a course in the summer? In the next section, we'll learn how to ask a more complex question of the data.