Amal Nagm

Use data from a related table to enhance the information in your query

Use the Query Wizard to build a query from a primary table and a related table

1. Ensure that the tables have a defined relationship (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) in the Relationships window (Relationships window: A window in which you view, create, and modify relationships between tables and queries.). How?
   
   1. On the Database Tools tab, in the Show/Hide group, click Relationships.
   2. On the Design tab, in the Relationships group, click All Relationships.
   3. 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.
        
   4. 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 (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) and a field that is of the Number data type (Number data type: In a Microsoft Access database, a field data type designed for numerical data that will be used in mathematical calculations. Use the Currency data type, however, to display or calculate currency values.), if that field has a long integer field size. This will often be the case when you are creating a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.).
      The Edit Relationships dialog box appears.
      
   5. 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.
      
   6. Close the Relationships window.

   ---

2. On the Create tab, in the Other group, click Query Wizard.
3. In the New Query dialog box, click Simple Query Wizard, and then click OK.
4. In the Tables/Queries combo box, click the table that contains the basic information you want included in your query.
5. 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.
6. In the Tables/Queries combo box, click the table that contains the related data you want to use to enhance your query results.
7. Add the fields that you want to use to enhance your query results to the Selected Fields list and then click Next.
8. 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.
   
9. Click Finish to view the results.

An example that uses the Northwind sample database

Use the Query Wizard to build the query

1. Open the Northwind sample database. Close the login form.
2. On the Create tab, in the Other group, click Query Wizard.
3. In the New Query dialog box, click Simple Query Wizard, and then click OK.
4. In the Tables/Queries combo box, click Table: Orders.
5. 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.
6. In the Tables/Queries combo box, click Table: Employees.
7. 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.
8. 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.
9. Click Finish to view the results.

Connect the data in two tables by using their relationships with a third table

Build a select query by using tables with a many-to-many relationship

1. On the Create tab, in the Other group, click Query Design. The Show Table dialog box opens.
   
2. In the Show Table dialog box, 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.
   
3. Double-click each of the fields that you want to use in your query results. Each field then appears in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).
4. 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.
5. 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.
6. On the Design tab, in the Results group, click Run. Access displays the query output in Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).
   

An example that uses the Northwind sample database

Build the query in Design view

1. Open the Northwind database. Close the login form.
2. On the Create tab, in the Other group, click Query Design. The Show Table dialog box appears.
   
3. In the Show Table dialog box, double-click Customers, Orders, and Order Details, and then click Close. All three tables appear in the query design workspace.
   
4. In the Customers table, double-click the City field to add it to the query design grid.
5. 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.
6. In the Order Details table, double-click the ShippedDate and the UnitPrice fields. The fields are added to the query design grid.
   
7. 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.
8. 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.
9. On the Design tab, in the Query Type group, click Crosstab. Two new rows, Total and Crosstab, appear in the query design grid.
   
10. 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).
    
11. 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).
    
12. 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).
    
13. 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.
    
14. On the Design tab, in the Results group, click Run.

View all the records from two similar tables

Create a union query by using two tables

1. On the Create tab, in the Other group, click Query Design. A new query design grid opens, and the Show Table dialog box appears.
   
2. In the Show Table dialog box, click Close.
3. 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.
   
4. 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.
5. Type FROM, followed by the name of the first of the tables you want in the query. Press ENTER.
6. 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.
7. Type UNION, and then press ENTER.
8. 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.
9. Type FROM, followed by the name of the second table you want to include in the query. Press ENTER.
10. If you want, add a WHERE clause, as described in step 6 of this procedure.
11. Type a semicolon (;) to indicate the end of your query.
12. On the Design tab, in the Results group, click Run. Your results appear in Datasheet view.