Pulling Data From Multiple Tables Into a Single Query in Ms Access by Nicholas Brown

<h1>Pulling Data From Multiple Tables Into a Single Query in Ms Access by Nicholas Brown</h1>Author: <a title="Nicholas Brown" href="http://www.articlesbase.com/authors/nicholas-brown/80872.htm">Nicholas Brown</a> Sometimes an Access database user needs to pull pieces of information from multiple table sources to display on a single form. For many users, this can seem like a daunting task. However, with a few tips and a little practice, you can learn to link database tables with little headache. The easiest way to accomplish this is to make use of the “Primary Key” option provided in MS Access. The Primary Key is a unique identifier that classifies the data in a table. By representing data in different tables with the same Primary Key, linking tables is relatively easy.
The first step is to create a new query. Once the query has been created and named, open the query in “Design View” to view the inner workings of the query. Since this is a new query, there should not be any tables listed in the viewer. Select the “add table or query” option to select the tables you would like to link.
 
If the Primary Key for the tables selected is the same for each table, then linking the tables is easy. Simply select the field set as the primary key and drag it to the corresponding table’s primary key. The tables are now linked by the uniquely identifying Primary Key. Information from either table can now be displayed in the query when it is run.
 
If the Primary Key differs between the selected tables, then a little more work is required to link the tables. Let us consider an example to better illustrate this process. Assume you have two tables. One table contains an automatically numbered Primary Key. The table contains one other field besides the Primary Key. This field contains descriptions of options a user can select (e.g. “yes”, “no”, “other”, etc.) stored as strings. This type of table is known as a “list” table or a “static” table because the data does not change. The other table contains account information for customers at a small business. One of the fields displays whether or not the customer has paid their outstanding bill.
In the newly created query, the user must link the two fields. The linking between the two tables can be accomplished using the “Criteria” option in the viewer. Below is an example of how to link the data in the dynamic and static tables:
 
Dynamic Table:
“[Static Table Name].[Static Table Field Name]”
 
The two tables have now been linked! Use this as a template for linking your own tables within a query. With a little practice, you will be able to create more complex queries, helping you become more efficient and organized within your database.
 About the Author: Nicholas Brown is a recognized authority on the subject of Access Databases. He is the founder of Database Technology Services (DTS). DTS is a leader in custom Access database development. DTS programmers create databases for corporations, small businesses and individuals. Visit <a href="http://www.dts-consultants.com" target="_blank">www.dts-consultants.com</a> to see all of the services DTS has to offer.Article Source: <a href="http://www.articlesbase.com/">ArticlesBase.com</a> - <a href="http://www.articlesbase.com/databases-articles/pulling-data-from-multiple-tables-into-a-single-query-in-ms-access-by-nicholas-brown-566797.html" title="Pulling Data From Multiple Tables Into a Single Query in Ms Access by Nicholas Brown">Pulling Data From Multiple Tables Into a Single Query in Ms Access by Nicholas Brown</a>

Sometimes an Access database user needs to pull pieces of information from multiple table sources to display on a single form. For many users, this can seem like a daunting task. However, with a few tips and a little practice, you can learn to link database tables with little headache. The easiest way to accomplish this is to make use of the “Primary Key” option provided in MS Access. The Primary Key is a unique identifier that classifies the data in a table. By representing data in different tables with the same Primary Key, linking tables is relatively easy.

The first step is to create a new query. Once the query has been created and named, open the query in “Design View” to view the inner workings of the query. Since this is a new query, there should not be any tables listed in the viewer. Select the “add table or query” option to select the tables you would like to link.

If the Primary Key for the tables selected is the same for each table, then linking the tables is easy. Simply select the field set as the primary key and drag it to the corresponding table’s primary key. The tables are now linked by the uniquely identifying Primary Key. Information from either table can now be displayed in the query when it is run.

If the Primary Key differs between the selected tables, then a little more work is required to link the tables. Let us consider an example to better illustrate this process. Assume you have two tables. One table contains an automatically numbered Primary Key. The table contains one other field besides the Primary Key. This field contains descriptions of options a user can select (e.g. “yes”, “no”, “other”, etc.) stored as strings. This type of table is known as a “list” table or a “static” table because the data does not change. The other table contains account information for customers at a small business. One of the fields displays whether or not the customer has paid their outstanding bill.

In the newly created query, the user must link the two fields. The linking between the two tables can be accomplished using the “Criteria” option in the viewer. Below is an example of how to link the data in the dynamic and static tables:

Dynamic Table:

“[Static Table Name].[Static Table Field Name]”

The two tables have now been linked! Use this as a template for linking your own tables within a query. With a little practice, you will be able to create more complex queries, helping you become more efficient and organized within your database.

Nicholas Brown is a recognized authority on the subject of Access Databases. He is the founder of Database Technology Services (DTS). DTS is a leader in custom Access database development. DTS programmers create databases for corporations, small businesses and individuals. Visit www.dts-consultants.com to see all of the services DTS has to offer.

Rate this Article:

0 / 5 stars - 0 vote(s)

Re-Publish

Article Source: http://www.articlesbase.com/databases-articles/pulling-data-from-multiple-tables-into-a-single-query-in-ms-access-by-nicholas-brown-566797.html

Article Tags: Development, Programming, Table, Oracle, Microsoft Access, Database, Visual Basic, SQL, Query, Recordset

Related Videos
Related Articles
Ask / Related Q&A

Play

How to Delete a Table in Access 2007

Play

How to Move Around a Table in Access 2007

Play

How to Navigate Between Tables in Access 2007

Play

How to Rename a Table in Access 2007

Play

How to use Microsoft Access - Creating a Database and a Table - part 3

Latest Databases Articles
More from Nicholas Brown

Build Service Oriented Composite Applications with new Book on Oracle SOA Suite 11g

By: Swati | 13/11/2009
Oracle’s SOA Suite 11g is an integrated, best-of-breed solution that helps build and manage large, highly demanding SOA projects. This book offers a hands-on approach to learning Oracle SOA Suite 11g and provides a comprehensive overview of the Oracle SOA Suite 11g Product Architecture.

How to Prevent and Recovery From Hard Drive Crash?

By: kmadhav | 11/11/2009
The worst situation of data loss may occur at any point of time to any system. When you computer gets crashed, it brings all of your critical files down to engrave with it. If you wonder how to retrieve lost, missing or inaccessible data from your computer hard drive, Data Recovery is the key of it. Recovery is possible in the majority of data loss situations. Following are some things that you should know if your entire life depends upon your valuable data

Database Driven Oil Industry Software - Yesterday and Today

By: Tom Gruich | 10/11/2009
The idea of creating database driven oil industry software programs for use in exploration and production was introduced in the early 1990s. The Petrotechnical Open Software Corp’s request for information in 1991 generated a great deal of positive feedback from major companies directly involved in drilling and refining, as well as a variety of support companies. Here’s a look at what’s available today.

What is Database?

By: Jameson Meer | 06/11/2009
It is one of the technological terms that a lot of individuals have become familiar to hearing at-work or while browsing the web is the database.

Practice Smart Growth by Switching To A Smart Solution: Remote DBA

By: Kashif Mukhtar | 06/11/2009
The safe and effective handling of corporate data is vital to the survival of any organization. Many critical functions of a company are dependent on the accessibility and reliability of data. Despite growing complexities, databases must remain stable regardless of the resources available to support them. The ultimate responsibility of ensuring the stability of the database environment falls on the Database Administrator (DBA).

blank dvd

By: bettypalmateerjh | 06/11/2009
Basically DVD is Digital Video disk and used to data storage. Generally DVD can store 4.2 GB.DVD-R is a recordable DVD format same as CD -R and DVD+R. DVD-R generally comes in two different standards i.e DVD –RG and DVD-RA.

Benefits of Interconnecting Database Design Knowledge with Financial Topics

By: Tom Gruich | 03/11/2009
Techniques to make and manage money are some of the most popular topics for database designers to explore on the web, so it is little wonder that so many database designers turn their attention to dealing with financial database design services.

SQL SERVER -2005

By: Mahesh A. Dahale | 02/11/2009
A database management, or DBMS, gives the user access to their data and helps them transform the data into information. Such database management systems include dBase, paradox, IMS, SQL Server and SQL Server. These systems allow users to create, update and extract information from their database.

Database Design Basics by Nicholas Brown

By: Nicholas Brown | 21/10/2008 | Databases
Tips and tricks on designing and implementing a database that can handle everything you demand.

Product Delivery: Think Like the Customer by Nicholas Brown

By: Nicholas Brown | 15/10/2008 | Customer Service
Tips and tricks on delivering a product or service that meets all of the needs of your client.

Ms Access Databases Don't Have to Look Unprofessional by Nicholas Brown

By: Nicholas Brown | 30/09/2008 | Databases
Tips and tricks to help maximize the usability of your database by improving the user interface.

Using Comments in Your Programming Source Code by Nicholas Brown

By: Nicholas Brown | 30/09/2008 | Programming
Save time and money by adding comments to your source code.

Increasing Small Business Productivity by Nicholas Brown

By: Nicholas Brown | 27/09/2008 | Small Business
Tips and tricks to help maximize business profits by increasing project efficiency and organization.

Importing External Access Database Tables Using Visual Basic by Nicholas Brown

By: Nicholas Brown | 18/09/2008 | Programming
A brief how-to on importing external database tables using Visual Basic code. This tool can help users to automate their databases, saving time and money.

Pulling Data From Multiple Tables Into a Single Query in Ms Access by Nicholas Brown

By: Nicholas Brown | 17/09/2008 | Databases
A brief how-to on linking multiple tables within a single query. With this tool, users can develop more complex queries, reducing the need for several queries. This will also reduce the size of the database making it more efficient and easier to navigate.

Becoming an Expert Ms Access Database Programmer

By: Nicholas Brown | 11/09/2008 | Programming
Tips and tricks on becoming your own Access database expert. Follow these guidelines and you will be well on your way to becoming a leader in database development.