【 Create Multiple Query in Access 】 Step by Step Guide ▷ 2022

Today, continues to be classified as one of the best programs available in the Microsoft office suite. Thanks to the fact that it provides an endless number of tools with which users have the possibility of carry out optimal management of any kind of technical level.

A) Yes, Microsoft Access It is estimated as an ideal software for search, sort and present information in a professional way. Taking into account that, through it, it will be very simple to create the best database documents, without the need for advanced knowledge of it. Also, allows you to rely on , , reports and queries.

In this sense, in the case of consultations, the program allows create a query from multiple tables. So, given its great utility, it is important to know each of the steps to follow in order to do it correctly. In addition, we will indicate the most important data to consider before building a multi-table query in Access.

What to consider before creating a multi-table query?

In Microsoft Accessqueries are defined as a way to search and collect information based on one or more tables to find detailed fundamentals of a given database. Which, work much better than a simple search or a search filter, since allows you to easily enter information from multiple tables.

Therefore, a well-designed query in Access, has the ability to provide information that could not even be found by examining each record in the data table. That is, for instance, you can use the customer table to find the address of one of them. However, on some occasions, the process to create queries of Access can be as easy as select certain fields from a table, assign some criteria and view the results.

However, in most cases, the data must be distributed in more than one table and that is why the software supports the possibility of Build a query to combine information from multiple sources (or multiple tables). But, beyond that and depending on the nature of the data, Access supports three different processes for creating a multi-table query..

Either: Using data from a parent table and a related table, connecting data from two tables through their relationships to a third table, or looking at all records from two similar tables. Which means, this program It has the ability to adapt to the requirements of all its users when creating a query for its database..

Learn step by step how to create a multi-table query in Access

As we indicated in the previous part, depending on certain factors, it is possible to perform a multi-table query in Access from three different methods.

Next, we will explain in detail:

Create a query of a parent table and a related table

To optimize the information in the query, it is sometimes recommended to use data from a related table. In this sense, one should use both a table that provides the required information as well as another table to extract additional data and with it, obtain more accurate, useful and effective results in the query.

Thus, the procedure to follow consists of:

• First of all, check that the tables have a defined relationship, in the Relationships group of the Database Tools tab. Like in the Relations group of the Design tab. To do this, check that the tables are visible in the window that opens, taking into account that a relationship appears as a line that connects the two tables in a common field.
• In case the tables are not visible in the Relations window, it is necessary to add them. To do so, go to the Design tab and in the group “Show or hide”tap on “Table names”. Thus, select each of the tables with a double click and then, click on close.
• Subsequently, go to the “Create” tab in the Access Options bar and Click on “Query Wizard” within the Queries group.
• Then a new dialog box will be displayed and there, you have to choose “Simple Query Wizard” and click OK.

• Following the above, in the “Tables/Queries” optionyou must open the dropdown menu and click on the table that contains the basic information to add in the query.
• Now, in the “Available Fields” listclick on the first field you want to add in the query for later move it to the “Selected Fields” list just by pressing the arrow to the right. You have to do this with each additional field of that table to add.
• done the above, in the “Tables/Queries” box, click on the table that contains the related information that you want to use to optimize the query results. Also, as in the previous case, add the fields to use to the “Selected Fields” list. next, click on the “Next” button.

• Thus, you will enter a new window where you have to select “Open…” or “Modify…”within “Do you want to open the query or modify the query design?”.

• To save the changes, just click click the finish buttonin order to be able to visualize the optimized results.

Create a select query with tables in a many-to-many relationship

It is a solution that allows to connect the data of two tables through their relations with a third table, therefore, It is known as a “many-to-many relationship”.. Typically, this is the way of creating a query when the first two tables are one-to-many link involving three tables. Assessing that, the third table is identified as “join table”.

So, to create a select query with tables in a many-to-many relationship, you have to perform the following steps:

• Initially, through the options bar, Click on the “Create” tab and select the “Query Design” option in the group inquiries.
• Then in the dialog show tableproceeds to choose the two tables that contain the data you want to add in the querylike the join table that links them. This, by double-clicking on each one of them and pressing the “Add” button.

• Done the above, now all three tables will appear in the query design workspace, mixed together in the corresponding fields. Thus, double click on each of the fields to use for the query results.
• Next, in the query design grid, use the “Criteria” row to set the field criteria. If you want to stop showing the field in the results, you have to uncheck the corresponding Show row checkbox.

• Now, to organize the results based on the values ​​of a field, click “Ascending” or “Descending” of the corresponding Sort rowin the layout grid.
• Finally, in the tab Design and the Results group, Click on “Run” and thus, the software will present the query result in Datasheet View.

Create a union query from two tables

In other cases, the user will need to combine the data from two tables that have two identical structures, but one of them is located in a different database. So that, use of a data sharing initiative is required to analyze them as a single set, instead of doing two separate analyses. That way, presentation will be improved.

Therefore, if you require this type of solution in your Access database document, you will need to carry out the following steps:

• Starting from the ribbon, click on the “Create” tab and press on “Query Design” from the Queries group.
• In the dialog that opens, click “Close”.
• Now in the tab Design in Query Tools, Go to the Query Type group and click on the “Union” option.

• Once this is done, automatically, the query changes from the view Layout in SQL view. In this last, it is necessary that you write “SELECT” together with a list of the fields of the first of the tables to include in the query. Whereas, field names have to be enclosed in square brackets and separated by commas.
• Once you have done this, you have to press the enter key and so the mouse cursor will go down to the next line in the view SQL.
• Subsequently, enter the word “FROM” followed by the name of the first of the tables you want to add in your Access query. So press again Enter.
• After, type “WHERE” to set criteria for a field in the first table. In addition, you must also enter the field name of a comparison operator (an equal sign and the criteria). press enter.
• followed by it, type “UNION” and press the Enter key again.
• Now, you have to write “SELECT” along with a list of the fields from the second table that you want to add in the query. They generally refer to the same fields added from the first table, and in the same order. like this, press Enter.
• As in the case of the other table, proceed to write “FROM” followed by the name of the second table to add in the Access queryto press Enter.
• If you need to add any determining criteria, use the term “WHERE” to do it as we explained before.
• Finally, enter a semicolon (;) to indicate the end of the query.
• Now it’s time to access the Design tab and click on “Run”, in the Results group. That’s it.