Ramapo Masters of Science 
in Educational Technology

Tools and Concepts of Data Analysis: Dataweb

Access: Multi-table Queries (home)

Overview | Creating a 2 Table Query | Adding a Table to an Existing Query | Relationships

Overview: Queries involving more than one table

In a relational database it is common to have more than one table containing related data. Queries are capable of simultaneously drawing data from two or more tables provided that the tables are logically constructed for such a purpose and that a relationship has been defined between the tables.

In order to form a relationship between tables each table should contain an primary key. This primary key is a field whose value is never repeated in the table ensuring a unique marker for that record in the table. Microsoft Access attempts to include a field named ID in each table. This field will be set to a data type of  auto-number to insure that this field always contains a unique value.

Two tables are joined through a relationship established between one or more field in each table. These fields must have logical relationship such as each being the userid associated with the data in the record.

Top | Home

Creating a New 2 Table Query

  • To open the database, Double Click the database file

Creating a 2 Table Query

  • If needed, Click the dropdown in the pane on the left | Object Type

Creating a 2 Table Query

  • If needed, Click the dropdown in the pane on the left | Queries

Creating a 2 Table Query

  • Create | Query Design

Creating a 2 Table Query

  • Select the first table you wish to add to the query | Add

Creating a 2 Table Query

  • Select the Second (or third, fourth, etc.) table you wish to add to the query | Add

Creating a 2 Table Query

  • After you have added all the tables you want, Click Close

 Creating a 2 Table Query

Top | Home

Adding a Table to an Existing Query

Once the database is open and you have switched to query view (See first three steps in section above.)

  • Double Click the query you wish to edit

Adding Tables to Existing Queries

  • Click the View Icon to switch to Design View

Adding Tables to Existing Queries

  • Click the Show Table Icon

Adding Tables to Existing Queries

  • Select the table or tables you wish to add | Add

Adding Tables to Existing Queries

  • After you have added all the tables you want, Click Close

Adding Tables to Existing Queries

Top | Home

Relationships

When you first create a multi-table query, MS Access may attempt to create a relationship between the tables. If this relationship is incorrect it must be deleted. To delete a relationship:

  • Select the relationship line by clicking it | Hit the delete key

Table Relationships in Queries

Relationships are key to creating a multi-table query. Tables must be joined through fields that match related records in each table. In this example, the user_id in each table marks information associated with a particular entry in the database. To create a relationship:

  • Click the desired field and hold the mouse button down

Table Relationships in Queries

  • Drag your mouse over the desired field in the other table and release the mouse button

Table Relationships in Queries

  • Note the line that forms the relationship between the tables

Table Relationships in Queries

Top | Home

Site Designed and Maintained by rsciorra
Last Updated 02/21/08