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

DSi Download Center - Get Nintendo DS And DSi Games For Free and Legally!

By: Sarah Brown | 24/12/2009
Many people are sick of buying expensive Nintendo DS and DSi games cartridge and they want to paly games while saving their money, so I will show you where you can download Nintendo DS and Dsi games for free and legally. Not many people are aware that copyrighted Nintendo DS and DSi games can be downloaded on the internet legally. DSi Download Center is a membership based program that has more than 250,000 Downloads making it the Biggest Database for the DSI!

Know the information USB sticks

By: John Milton | 24/12/2009
Now in the market there is high demand of usb sticks among its users. Seeing this the producers of usb sticks now focussing more on its reliability.

Download Jak and Daxter The Lost Frontier PSP Game

By: Sarah Brown | 23/12/2009
PSP UMD discs are becoming quite expensive these days, many gamers are seeking for new alternatives to get the newest and their favorite PSP games for cheap. This article will expose on how and where you can download Jak and Daxter The Lost Frontier for less than $0.01. Jak and Daxter The Lost Frontier is already released on PSP handheld, so you can proceed to download the game instantly.

A Photography Course For All The Family

By: Dan Feildman | 23/12/2009
Excellent Digital Photography Courses Introducing Free Digital Photography Guide called "How To Take Beautiful Digital Pics Made Simple". The latest tips on how to use a digital camera like a pro.

Reinforce Database Management with Remote DBA

By: Kashif Mukhtar | 23/12/2009
Databases are the foundation of the digital economy. They store the information that run businesses and organize the vast amount of data that is created on a regular basis. Even though e-business infrastructures are requiring stronger database support to sustain their 24x7 operations, shrinking IT budgets are forcing companies to reduce the size of their DBA staff. With databases growing in size and complexity, the role of the already overburdened DBA is becoming increasingly difficult.

Download Full Version Movies - Downloading Movies Have Never Been Easier - Download as much and as often as you like for one fee!

By: Dimitar Mishev | 22/12/2009
We as movie watchers have come a long way since the introduction of movie tapes and watching movies from our home. From beta max to the VCR tapes, we have rented and recorded thousands of movies and still do.

Download Dvd Movies Safely - If You Are Looking To Download Dvd Movies, Check Out These Tips - Amount of Content Available

By: Dimitar Mishev | 22/12/2009
There are so many of us now days that are trying to find ways to save some money, and one of these ways is cutting back on movie purchases. If you are one of these then you will be happy to know that there are places that you can download DVD movies right on the Internet. And, not only can you download some great movies, you can also burn them to a disc and play them in a DVD player and your computer.

Download Movies Full Length In Your Own Home - Why Are You Wasting Time At The Video Store - Have you seen the price of DVD's lately?

By: Dimitar Mishev | 22/12/2009
You are no longer stuck with the local video store on the corner, which by the way is always out of the new releases, to get your movie rentals. You can now download movies full length right onto your computer and even burn them to disc.

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.