The entire purpose of related tables is that they allow you to query information from multiple tables at once. In this example we will be creating a query that looks at the Class and Teacher tables we built in the intro to MS Access Lesson: link to lesson
To start, we will select Create from the Menu bar and Query Design from the Ribbon
Next drag the Teacher and Class table over to the blank space for Query1
down below you can select the columns (fields) you want to bring in from the tables.
Next, put all the all the available columns in the Fields below
Right click on the query tab and select datasheet view.
Note the two tables are matched up by the teacherID in two different tables. That is the relationship. That is how relational database allow tables to interact
Lets remove the teacherID from the query
Go back to design view and uncheck the columns to hide them from the query
To see the results, click on the Query1 tab and select Datasheet view
So you can see, our query returned information found in 2 different tables
Now, let’s right click on Query1 tab and select SQL View
This is how you would write this query using SQL
Select Class.ClassID, Class.Class_NM, teacher.teacher_name — This means that we want to see these three columns. Note the table name is in front, followed by the column or field name, separated by a ‘.’ This is common practice in SQL. It tells the database which table the field is in. And in situations like the teacherID column that is found in both tables, it clarifies which one you want.
from teacher INNER JOIN Class ON teacher.teacherID= Class.TeacherID; — this is a typical join statement. It says use both teacher and Class tables, and match the records up using the TeacherID field.
For more information in Joins, check my SQL Intro to Joins and SQL 4 Types of Joins
Back to Main Course Page: Course