Today, Access continues to be cataloged as one of the best programs available in Microsoft's office suite. Thanks to the fact that it provides endless tools with which users have the possibility of perform optimal management of any kind of data at a technical level.
So, Microsoft Access is considered 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, supports tables, forms, reports and queries.
In this sense, in the case of consultations, the program allows create a query from multiple tables. Therefore, 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 facts to consider before building a multi-table query in Access.
What to consider before creating a multi-table query?
In Microsoft Access, the queries are defined as a way to find and collect information based on one or more tables to find detailed basics 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 ascertained by examining each of the records in the data table. That is, for example, you can use the customer table to find the address of one of them. Now, on some occasions, the process for creating queries for Access can be as easy as select certain fields in a table, target 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 allows three different processes to create a multi-table query.
Either: Using the data of a main table and a related table, connecting the data of two tables through their relations with a third table or observing all the records of two similar tables. Which means that, 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 query of multiple tables in Access from three different methods.
Next, we will explain you in detail:
Create a query of a main table and a related table
To optimize the information in your query, it is sometimes recommended to use data from a related table. In that sense, both a table that provides the required information as well as another table to extract additional data and with it, obtain more precise, useful and effective results in the consultation.
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 Relationships 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 Relationships window, it is necessary to add them. To do so, go to the Design tab and in the group "Show or hide", click on "Table names". Thus, double-click each of the tables and then, click Close.
- Later, go to the "Create" tab in the Access options bar and click on "Consultation Wizard" within the Queries group.
- Then a new dialog will be shown and there, you have to choose "Simple Query Wizard" and click OK.
- Followed by the above, in the option "Tables / Queries", you must open the drop-down menu and click on the table that contains the basic information to add in the query.
- Now, in the "Available fields" list, click 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. This you have to do with each additional field of that table to add.
- Done the above, in the box "Tables / Queries", 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. Then click 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 the Finish button, in order to visualize the optimized results.
Create a select query with tables in a many-to-many relationship
It is a solution that allows you to connect the data of two tables by means of their relationships with a third table, therefore, It is known as a "many-to-many relationship". Generally, this is the way to build a query when the first two tables are linked one-to-many involving three tables. Valuing 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 option "Query Design" in the group Queries.
- Then in the dialog Show table, proceed to choose the two tables that contain the data you want to add in the query, like the join table that links them. This, by double clicking on each 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. So, double-click on each of the fields to use for the query results.
- Then in the query design grid, use the row "Criteria" to set the field criteria. If you want to stop showing the field in the results, you have to disable the checkbox of the corresponding Show row.
- Now, to organize the results based on the values of a field, click on "Ascending" or "Descending" of the corresponding Sort row, on 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 required to analyze them as a single set, instead of doing two separate analyzes. 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 with the ribbon, click on the "Create" tab and press on "Query design" of the Queries group.
- In the dialog that opens, click "Close".
- Now, in the tab Design in Query Tools, Go to the Type of query group and click on the "Union" option.
- Done the above, automatically, the query changes the view Layout in SQL view. In this last, You need to 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 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.
- Later, 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, write "WHERE" to set criteria for a field in the first table. Additionally, 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 hit 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 to the query. Usually they refer to the same added fields from the first table and in the same order. So, 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 query, to press Enter.
- If you need to add any determining criteria, use the term "WHERE" to do it as we explained above.
- By last, enter a semicolon sign (;) 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.
