Pulling Data From Multiple Tables Into a Single Query in MS Access

Sometimes an Access database user needs to pull pieces of information from multiple table sources to display on a single form. For many users, this can seem like a daunting task. However, with a few tips and a little practice, you can learn to link database tables with little headache. The easiest way to accomplish this is to make use of the "Primary Key" option provided in MS Access. The Primary Key is a unique identifier that classifies the data in a table. By representing data in different tables with the same Primary Key, linking tables is relatively easy.

The first step is to create a new query. Once the query has been created and named, open the query in "Design View" to view the inner workings of the query. Since this is a new query, there should not be any tables listed in the viewer. Select the "add table or query" option to select the tables you would like to link.

If the Primary Key for the tables selected is the same for each table, then linking the tables is easy. Simply select the field set as the primary key and drag it to the corresponding table's primary key. The tables are now linked by the uniquely identifying Primary Key. Information from either table can now be displayed in the query when it is run.

If the Primary Key differs between the selected tables, then a little more work is required to link the tables. Let us consider an example to better illustrate this process. Assume you have two tables. One table contains an automatically numbered Primary Key. The table contains one other field besides the Primary Key. This field contains descriptions of options a user can select (e.g. "yes", "no", "other", etc.) stored as strings. This type of table is known as a "list" table or a "static" table because the data does not change. The other table contains account information for customers at a small business. One of the fields displays whether or not the customer has paid their outstanding bill.

In the newly created query, the user must link the two fields. The linking between the two tables can be accomplished using the "Criteria" option in the viewer. Below is an example of how to link the data in the dynamic and static tables:

Dynamic Table:

"[Static Table Name].[Static Table Field Name]"

The two tables have now been linked! Use this as a template for linking your own tables within a query. With a little practice, you will be able to create more complex queries, helping you become more efficient and organized within your database.