How to Create Multi-Table Queries in Microsoft Access on Windows 11
Posted on 16th June 2023
Introduction
Microsoft Access is a relational database management system (RDBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.
Multi-table queries are a powerful feature in Microsoft Access that allows you to combine data from multiple tables into a single recordset. By using a multiple table query, you can select fields from different tables and put them all into one recordset.
In this article, we will show you how to create a multi-table query in Microsoft Access on Windows 11.
Step 1: Open Microsoft Access
First, you need to open Microsoft Access on your computer. If you don’t have Microsoft Access installed on your computer, you can download and install it from the Microsoft website.
Step 2: Create a New Query
Next, you need to create a new query. To do this, click on the “Create” tab and then click on the “Query Design” icon.
Step 3: Add Tables to the Query
After you have created a new query, you need to add tables to the query. To do this, click on the “Add Tables” icon and then select the tables that you want to add to the query.
In this example, we are going to add two tables to the query: “tbl_Employees” and “tbl_Orders”.
Step 4: Select Fields to Add to the Query
After you have added tables to the query, you need to select fields to add to the query. To do this, double-click on the fields that you want to add or click and drag the fields to the “Field” section of the query.
In this example, we are going to add the “FirstName”, “LastName”, and “OrderID” fields to the query.
Step 5: Run the Query
After you have added fields to the query, you need to run the query. To do this, click on the “Run” icon.
The query will now run and you will see the results in the “Result” section of the query.
Step 6: Save the Query
After you have run the query, you need to save the query. To do this, click on the “File” menu and then click on the “Save” icon.
In the “Save As” dialog box, enter a name for the query and then click on the “Save” button.
Step 7: Close the Query
After you have saved the query, you need to close the query. To do this, click on the “File” menu and then click on the “Close” icon.
You have now successfully created a multi-table query in Microsoft Access on Windows 11.
When you want to combine data from multiple tables into a single query result, you create a multi-table query. A multi-table query is a query that retrieves data from two or more tables. The tables that you want to query must have a common field, such as a product ID field, to link the data in the tables.
In Access, you create multi-table queries in the query design grid. The query design grid is a tool that you use to add fields from multiple tables to a query, set relationships between the tables, and specify query criteria. You can also use the query design grid to create calculated fields, to change the order of the fields in the query results, and to group and sort the data.
To create a multi-table query:
1. Open the query design grid for the first table that you want to query. To do this, on the ribbon, click Create, and then click Query Design. In the Show Table dialog box, double-click the table that you want to query.
2. In the query design grid, double-click the fields that you want to query to add them to the design grid.
3. Repeat step 1 for each additional table that you want to query.
4. In the query design grid, click the join line that connects the common field in the first table to the common field in the second table. On the Join Properties tab, in the Join Type group, click the type of join that you want to create.
5. If you are querying more than two tables, repeat step 4 for each pair of tables that you are querying.
6. In the query design grid, in the Criteria row of the field that you want to use to specify query criteria, type the criteria that you want to use. For example, if you want to query for all products with a unit price greater than $10, you would type >10 in the Criteria row of the UnitPrice field.
After you have added all of the fields and tables that you want to query, and you have specified the relationships between the tables, you can run the query to view the query results. To do this, on the ribbon, click Run.
To add a second table to your query, open the query in Design view. In the upper pane of the query design window, click the Tables/Queries drop-down arrow and select the table you want to add.
If your query will return data from more than one table, you need to specify the relationships between the tables. To do this, click the Relationships button on the Design tab. In the Show Table dialog box, double-click the tables you want to include in your query and click Close.
In the Relationships window, click and drag the field you want to use to join the tables from one table to the field with the same name in the other table. For example, to join the Customers and Orders tables, you would click and drag the CustomerID field from the Customers table to the CustomerID field in the Orders table.
When you have finished adding tables and relationships, click Close to return to the query design window.
In the query design window, you can now add the fields you want to include in your query. To do this, click the Fields drop-down arrow and select the table you want to add fields from. Then, double-click the fields you want to include in your query.
You can also add criteria to your query. To do this, click in the Criteria cell for the field you want to apply criteria to and enter the criterion. For example, to find all orders for customers in the state of California, you would enter CA in the Criteria cell for the State field.
When you have finished adding fields and criteria, click the Run button on the Design tab to run your query.
