Getting the Data from Multiple Data Tables into a Single Access Query
Lisa Mills
November 07, 2009
Most of the times, the MS Access database users have to extract different information pieces from multiple data table sources to display all that information together on a single form. Most users feel that this is a tedious procedure. But one can easily learn the method of linking the database tables by remembering a few tips about access query and putting them to practice. The simplest way to achieve this is by using the identifier "Primary Key", given in MS Access. It uniquely identifies a data in the table. You can link multiple data tables by using the same Primary Key to represent he data in different tables.
The first step for linking tables is the creation of a new access query. After naming it, open the query in the "Design View" in which you can see how the query is designed and how it works. There should not be any tables created in this viewer. Now select the "add query or table" option for selecting the tables to be linked. Now you can see the importance of a primary key. Selecting the tables to be added becomes lot more simple if you have used the same Primary Key as identifier in all the tables. All you need to do is to select the field set as the primary key in a table and drag it to the corresponding table´s primary key and your tables are linked. Now whenever you run the query, you can display information from either of the tables.
But in case, you are not using a common primary key for all your tables, then you are required to perform some extra steps to link the desired tables. Let us take an example of two tables; of which one table has primary key which is automatically numbered. This table also has a field storing the option´s description in the string form that the user can select. The other table stores the information related to accounts or customers having a small scale business and one of its fields shows if a certain customer has paid the bill or not.
Now, after creating a new query you can link the above two tables. This can be done using the "criteria" option given in the viewer. You can start practicing the linking method using simple queries and slowly move on to using complex queries.
The first step for linking tables is the creation of a new access query. After naming it, open the query in the "Design View" in which you can see how the query is designed and how it works. There should not be any tables created in this viewer. Now select the "add query or table" option for selecting the tables to be linked. Now you can see the importance of a primary key. Selecting the tables to be added becomes lot more simple if you have used the same Primary Key as identifier in all the tables. All you need to do is to select the field set as the primary key in a table and drag it to the corresponding table´s primary key and your tables are linked. Now whenever you run the query, you can display information from either of the tables.
But in case, you are not using a common primary key for all your tables, then you are required to perform some extra steps to link the desired tables. Let us take an example of two tables; of which one table has primary key which is automatically numbered. This table also has a field storing the option´s description in the string form that the user can select. The other table stores the information related to accounts or customers having a small scale business and one of its fields shows if a certain customer has paid the bill or not.
Now, after creating a new query you can link the above two tables. This can be done using the "criteria" option given in the viewer. You can start practicing the linking method using simple queries and slowly move on to using complex queries.
Author writes for ms access, microsoft access and access query.