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
Step2. To add tblSections and tblCourses, in the Show Table dialog box,
Double-Click tblSections, Double-Click tblCourses, Click
NOTE: If you desire, to see all of each table's fields, you can resize them here.
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,
Step2. To add the CourseTitle field from tblCourses,
Step3. To view the query results, on the Ribbon,
Step4. Resize the columns as you see fit in order to see the data more clearly.
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:
Step7. To hide the SemesterYear column,
Step8. To save the query, press:
Step9. To name the query, type:
qryCoursesAndSectionsSummer18, press: Enter
Step10. To view the query, on the Ribbon,
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.
Step2. To save the query with a new name, press:
F12 key, type: qryFacultyCoursesSectionsSummer18, press: Enter
Step3. Switch to Design view.
Step4. To add tblFaculty to the query, on the Design tab of the Ribbon,
Click, Double-Click tblFaculty, Click
NOTE: You can Press & Drag tblFaculty from the Navigation pane into the query designer, too.
Step5. Adjust the tables in the query designer as you see fit.
NOTE: You may need to rearrange the tables to see them in this order.
Step6. To add the first and last name of the instructor, in tblFaculty,
Double-Click LastName, Double-Click FirstName
Step7. Reorder the fields however you see fit.
Step8. To view the query, on the Ribbon,
Step9. Save qryFacultyCoursesSectionsSummer18.