Let’s learn the ways to create Queries in MS Access that can refer to multiple tables.
Queries for single table are very common in Microsoft Access but sometimes there are cases when we are required to make queries that can refer to multiple Tables. Users often face the requirement of data that is present in more than one table.
Fortunately, Microsoft Access provides the feature of creating multi-level query. Users can create a query that can combine data and information from multiple sources. The following article will explain in detail the simple procedure to create a Multiple level query.
Multiple Table Queries
To understand the need of a query for multiple tables we can take an example of a management person who wishes to inspect the lists of all the customers of the company and the details of their accounts bank. It’s a typical example of one to many relationships. Now, to provide the manager with the desired query, we need both the tables including customers and their bank account details. In such cases, we require multiple data queries for better information sharing and the results will be clear and more useful.
In Access, it’s quite a simple procedure to perform but users need to first create a relationship between the two tables. To check that, you can simply select Database Tools tab and click on Relationships in Show/Hide Group. Then, in ‘Design’ tab click on ‘All Relationships’ under the relationships section. There you can see the defined relationships. If it’s not defied then you should first create a relationship between the tables before moving on to the Multiple Table Query.
For the above example, the required tables are of customers and their account details.
How to Run the Query
To create a multiple table query, you have to select the ‘Query Wizard’ button from the ‘Create’ tab. It will create a new Query and you can select the option of ‘Simple Query Wizard’. Access will display the Simple Query Wizard window which will show two lists viz. Available fields list and Selected Fields list. You can add fields to selected fields by simply selecting them from Available fields and clicking the right arrow ‘>’ button.
For the above example, you can select fields like CustomerID & LastName from Customer table and from Accounts table; you can select CustomerID, Balance and Account Type fields. All these fields will be displayed in the Selected fields space. To continue, click the Next button.
The Wizard will then show you a window providing options of summary and Detail field. Select either Detail or Summary field. For the above example, Detail Field is appropriate. To continue and run the Query click Next.
In the next step you will be asked to Name your Query. Either leave it to the default name or give any name as per your wish and click Finish. Also, select the option of ‘Open the query to view information’ instead of Modifying option to view results.
MS Access will automatically join the Customers table to the Accounts table as a result of this query and according to the relationship created between these two tables.
Database crashes in MS Access can occur at any point in time without warning. Hence professional Access users should always keep an accdb recovery tool handy to deal with such situations.
Author Introduction:
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix SQL Server and excel recovery software products. For more information visit www.datanumen.com