Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Creating a Multiple Table Select Query

In a properly normalized database, multiple table queries are the rule rather than the exception. A single table will seldom have all the information you need so it may be necessary to combine two, three, or more tables into a single query.

Changes to the Database

The demonstration database has been updated and is available for download from here.

The database now contains, among other things, a normalized employee table and additional tables that a normalized structure requires. All tables now have primary keys and relationships have been established between all the tables.

A Simple ExampletblEmployees_FieldList

Let’s start by looking at a simple query on the Employees Table. Here is the table’s field list. The table contains most of the essential information that we want to keep about each employee except the employee’s actual name and job title.

So, if we were to create a query just using the Employees Table, this is what it would look like when we view it in datasheet view.

 

qselEmployees

It’s pretty difficult to know to which employee the Home Phone, Extension, and Notes refer. The two fields, PersonID and JobTitleID are referred to as foreign keys because they indicate the record in another table that has related information. We can get that information by including the required table to the query.

qselEmployeeNames_Design

By including the People Table in the query design, we can now display the correct name for each employee record:

qselEmployeeNames

Because a relationship exists between People and Employees, the two tables are correctly connected (or joined) as soon as the People Table is added to the query design. If we wanted to know the employee’s job title in this example, we would also have to include the Job Titles table in the query design.

Incidentally, you can add tables and queries to a query design by dragging the required object from the Database Window (Access 2003 and earlier) or Navigation Pane (Access 2007/2010) or by right-clicking in the field list area in query design view and selecting Show Table to open the Show Table dialogue.

Comments are closed.