In the query design window, in the table area, the list of field names in a table.
Sometimes, the process of building and using queries in Access is a simple matter of selecting fields from a table, perhaps applying some criteria, and then viewing the results. But what if, as is more often the case, the data you need is spread out in more than one table? Fortunately, you can build a query that combines information from multiple sources. This topic explores some scenarios where you pull data from more than one table, and demonstrates how you do it. Show
What do you want to do?
Use data from a related table to enhance the information in your queryYou may have cases in which a query that is based on one table gives you the information you need, but pulling data from another table would help to make the query results even clearer and more useful. For example, suppose you have a list of employee IDs that appear in your query results. You realize it would be more useful to view the employee name in the results, but the employee names are located in a different table. To get the employee names to appear in your query results, you need to include both tables in your query. Use the Query Wizard to build a query from a primary table and a related table
An example that uses the Northwind sample databaseIn the following example, you use the Query Wizard to build a query that displays a list of orders, the shipping fee for each order, and the name of the employee that handled each order. Note: This example involves modifying the Northwind sample database. You may want to make a backup copy of the Northwind sample database and then follow this example by using that backup copy. Use the Query Wizard to build the query
The query returns a list of orders, each with its shipping fee and the first and last name of the employee who handled it. Top of Page Connect the data in two tables by using their relationships with a third tableOften, data in two tables are related to each other through a third table. This is usually the case because the data between the first two tables are related in a many-to-many relationship. Often, it is good database design practice to split a many-to-many relationship between two tables into two one-to-many relationships involving three tables. You do this by creating a third table, called a junction table or a relationship table, that has a primary key and a foreign key for each of the other tables. A one-to-many relationship is then created between each foreign key in the junction table and the corresponding primary key of one of the other tables. In such cases, you need to include all three tables in your query, even if you want to retrieve data from only two of them. Build a select query by using tables with a many-to-many relationship
An example that uses the Northwind sample databaseNote: This example involves modifying the Northwind sample database. You may want to make a backup copy of the Northwind sample database, and then follow this example by using the backup copy. Suppose you have a new opportunity: a supplier in Rio de Janeiro has found your Web site, and might want to do business with you. However, they only operate in Rio and nearby São Paulo. They supply every category of food product that you broker. They are a fairly big business, and want your assurances that you can provide them access to enough potential sales to make it worthwhile: at least R$20,000.00 per year in sales (about $9,300.00). Can you provide them with the market they require? The data you need to answer this question is found in two places: a Customers table and an Order Details table. These tables are linked to each other by an Orders table. Relationships between the tables have already been defined. In the Orders table, each order can have only one customer, related to the Customers table on the CustomerID field. Each record in the Order Detail table is related to only one order in the Orders table, on the OrderID field. Thus, a given customer can have many orders, each of which has many order details. In this example, you will build a crosstab query that displays total sales per year, in the cities Rio de Janeiro and São Paulo. Build the query in Design view
You now have a query that returns the total sales by year in Rio de Janeiro and São Paulo. Top of Page View all the records from two similar tablesSometimes, you will want to combine data from two tables that are identical in structure, but one of them is located in another database. Consider the following scenario. Suppose you are an analyst working with student data. You are embarking on a data sharing initiative between your school and another school, so that both schools can improve their curricula. For some of the questions you want to explore, it would be better to look at all records from both schools together, rather than each school's records separately. You could import the other school's data into new tables in your database, but then any changes to the other school's data would not be reflected in your database. A better solution would be to link to the other school's tables, and then create queries that combined the data when you run them. You would be able to analyze the data as a single set, rather than performing two analyses and trying to interpret them as if they were one. To view all the records from two tables with identical structure, you use a union query. Union queries cannot be displayed in Design view. You build them by using SQL commands that you enter in a SQL view object tab. Create a union query by using two tables
Top of Page See AlsoJoin tables and queries Can access relationship where one record in the first table corresponds to many records in the second table?Glossary. Which view displays data in columns and rows similar to the format of an Excel worksheet?Chapter 1. Is a field or set of fields that uniquely identifies the record?A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key.
When information is repeated in a database in a manner that indicates poor design the data is said to be?Glossary. |