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.
What do you want to do?
Use data from a related table to enhance the information in your query
You 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
-
Ensure that the tables have a defined relationship in the Relationships window.
How?
-
On the Database Tools tab, in the Show/Hide group, click Relationships.
-
On the Design tab, in the Relationships group, click All Relationships.
-
Identify the tables that should have a defined relationship.
-
If the tables are visible in the Relationships window, check to see that a relationship has already been defined.
A relationship appears as a line connecting the two tables on a common field. You can double-click a relationship line to see which fields in the tables are connected by the relationship.
-
If the tables are not visible in the Relationships window, you must add them.
On the Design tab, in the Show/Hide group, click Table Names.
Double-click each of the tables you want to show, and then click Close.
-
-
If you do not find a relationship between the two tables, create one by dragging a field from one of the tables to a field on the other table. The fields on which you create the relationship between the tables must have identical data types.
Note: You can create a relationship between a field that is of the AutoNumber data type and a field that is of the Number data type, if that field has a long integer field size. This will often be the case when you are creating a one-to-many relationship.
The Edit Relationships dialog box appears.
-
Click Create to create the relationship.
For more information about the options you have when you create a relationship, see the article Create, edit or delete a relationship.
-
Close the Relationships window.
-
-
On the Create tab, in the Queries group, click Query Wizard.
-
In the New Query dialog box, click Simple Query Wizard, and then click OK.
-
In the Tables/Queries combo box, click the table that contains the basic information you want included in your query.
-
In the Available Fields list, click the first field you want to include in your query, and then click the single right arrow button to move that field to the Selected Fields list. Do the same with each additional field from that table that you want to include in your query. These can be fields that you want returned in the query output, or fields that you want to use to limit the rows in the output by applying criteria.
-
In the Tables/Queries combo box, click the table that contains the related data you want to use to enhance your query results.
-
Add the fields that you want to use to enhance your query results to the Selected Fields list and then click Next.
-
Under Would you like a detail or summary query?, click either Detail or Summary.
If you do not want your query to perform any aggregate functions (Sum, Avg, Min, Max, Count, StDev, or Var), choose a detail query. If you do want your query to perform an aggregate function, choose a summary query. After you make your choice, click Next.
-
Click Finish to view the results.
An example that uses the Northwind sample database
In 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
-
Open the Northwind sample database. Close the login form.
-
On the Create tab, in the Queries group, click Query Wizard.
-
In the New Query dialog box, click Simple Query Wizard, and then click OK.
-
In the Tables/Queries combo box, click Table: Orders.
-
In the Available Fields list, double-click OrderID to move that field to the Selected Fields list. Double-click Shipping Fee to move that field to the Selected Fields list.
-
In the Tables/Queries combo box, click Table: Employees.
-
In the Available Fields list, double-click FirstName to move that field to the Selected Fields list. Double-click LastName to move that field to the Selected Fields list. Click Next.
-
Because you are creating a list of all the orders, you want to use a detail query. If you are summing shipping fee by employee or performing some other aggregate function, you use a summary query. Click Detail (shows every field of every record), and then click Next.
-
Click Finish to view the results.
The query returns a list of orders, each with its shipping fee and the first and last name of the employee who handled it.
Connect the data in two tables by using their relationships with a third table
Often, 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
-
On the Create tab, in the Queries group, click Query Design.
-
Double-click the two tables that contain the data you want to include in your query and also the junction table that links them, and then click Close.
All three tables appear in the query design workspace, joined on the appropriate fields.
-
Double-click each of the fields that you want to use in your query results. Each field then appears in the query design grid.
-
In the query design grid, use the Criteria row to enter field criteria. To use a field criterion without displaying the field in the query results, clear the check box in the Show row for that field.
-
To sort the results based on the values in a field, in the query design grid, click Ascending or Descending (depending on which way you want to sort the records) in the Sort row for that field.
-
On the Design tab, in the Results group, click Run.
Access displays the query output in Datasheet view.
An example that uses the Northwind sample database
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 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
-
Open the Northwind database. Close the login form.
-
On the Create tab, in the Queries group, click Query Design.
-
Double-click Customers, Orders, and thenOrder Details.
All three tables appear in the query design workspace.
-
In the Customers table, double-click the City field to add it to the query design grid.
-
In the query design grid, in the City column, in the Criteria row, type In ("Rio de Janeiro","São Paulo"). This causes only those records where the customer is in one of these two cities to be included in the query.
-
In the Order Details table, double-click the ShippedDate and the UnitPrice fields.
The fields are added to the query design grid.
-
In the ShippedDate column in the query design grid, select the Field row. Replace [ShippedDate] with Year: Format([ShippedDate],"yyyy"). This creates a field alias, Year, that allows you to use just the year portion of the value in the ShippedDate field.
-
In the UnitPrice column in the query design grid, select the Field row. Replace [UnitPrice] with Sales: [Order Details].[UnitPrice]*[Quantity]-[Order Details].[UnitPrice]*[Quantity]*[Discount]. This creates a field alias, Sales, that calculates the sales for each record.
-
On the Design tab, in the Query Type group, click Crosstab.
Two new rows, Total and Crosstab, appear in the query design grid.
-
In the City column in the query design grid, click the Crosstab row, and then click Row Heading.
This makes city values appear as the row headings (that is, the query returns one row for each city).
-
In the Year column, click the Crosstab row, and then click Column Heading.
This makes year values appear as the column headings (that is, the query returns one column for each year).
-
In the Sales column, click the Crosstab row, and then click Value.
This makes sales values appear at the intersection of rows and column (that is, the query returns one sales value for each combination of city and year).
-
In the Sales column, click the Totals row, and then click Sum.
This causes the query to sum the values in this column.
You can leave the Totals row for the other two columns at the default value of Group By, because you want to see each value for these columns, not aggregate values.
-
On the Design tab, in the Results group, click Run.
You now have a query that returns the total sales by year in Rio de Janeiro and São Paulo.
View all the records from two similar tables
Sometimes, 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
-
On the Create tab, in the Queries group, click Query Design.
-
On the Design tab, in the Query Type group, click Union.
The query switches from Design view to SQL view. At this point, the SQL view object tab is empty.
-
In SQL view, type SELECT, followed by a list of the fields from the first of the tables you want in the query. Field names should be enclosed in square brackets, and separated by commas. When you have finished typing the field names, press ENTER. The cursor moves down one line in SQL view.
-
Type FROM, followed by the name of the first of the tables you want in the query. Press ENTER.
-
If you want to specify a criterion for a field from the first table, type WHERE, followed by the field name, a comparison operator (usually, an equals sign (=)), and the criterion. You can add additional criteria to the end of the WHERE clause by using the AND keyword and the same syntax used for the first criterion; for example, WHERE [ClassLevel]="100" AND [CreditHours]>2. When you are finished specifying criteria, press ENTER.
-
Type UNION, and then press ENTER.
-
Type SELECT, followed by a list of the fields from the second table you want in the query. You should include the same fields from this table that you included from the first table, and in the same order. Field names should be enclosed in square brackets, and separated by commas. When you have finished typing the field names, press ENTER.
-
Type FROM, followed by the name of the second table you want to include in the query. Press ENTER.
-
If you want, add a WHERE clause, as described in step 6 of this procedure.
-
Type a semicolon (;) to indicate the end of your query.
-
On the Design tab, in the Results group, click Run.
Your results appear in Datasheet view.
