Create a query based on multiple tables

Маңызды :  This article is machine translated, see the disclaimer. Please find the English version of this article here for your reference.

Sometimes, the process of building and using queries 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.

Не істегіңіз келеді?

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

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

View all the records from two similar tables

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

  1. Ensure that the tables have a defined қатынас in the Қатынастар терезесі.

    Қалай?

    1. On the Database Tools tab, in the Show/Hide group, click Relationships.

    2. Құрастырушы қойындысындағы Қатынастар тобында Барлық қатынастар түймешігін басыңыз.

    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.

      Ескерту : You can create a relationship between a field that is of the Автосанағыш деректерінің түрі and a field that is of the Нөмірлі деректер түрі, if that field has a long integer field size. This will often be the case when you are creating a «берден көпке» қатынасы.

      Қатынастарды өңдеу диалогтық терезесі пайда болады.

    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

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.

Ескерту : 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

  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.

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 «көптен-көпке» қатынасы. 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 негізгі кілт and a сыртқы кілт 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

  1. Жасау қойыншасының Басқа тобындағы Сұраулар құрастырушысы түймешігін басыңыз.

    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 құрастыру торы.

  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. Құрастырушы қойындысындағы Нәтижелер тобында орналасқан Орындау түймешігін басыңыз.

    Access displays the query output in деректер кестесінің көрінісі.

An example that uses the Northwind sample database

Ескерту : 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

  1. Open the Northwind database. Close the login form.

  2. Жасау қойыншасының Басқа тобындағы Сұраулар құрастырушысы түймешігін басыңыз.

    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. Құрастырушы қойындысында Сұрау түрі тобының Қарсы тармағын таңдаңыз.

    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. Құрастырушы қойындысындағы Нәтижелер тобында орналасқан Орындау түймешігін басыңыз.

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 queries cannot be displayed in Design view. You build them by using SQL commands that you enter in a SQL көрінісі object tab.

Create a union query by using two tables

  1. Жасау қойыншасының Басқа тобындағы Сұраулар құрастырушысы түймешігін басыңыз.

    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. БІРІКТІРУ тармағын түртіп, 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. Құрастырушы қойындысындағы Нәтижелер тобында орналасқан Орындау түймешігін басыңыз.

    Your results appear in Datasheet view.

Беттің жоғары бөлігі

Ескерту : Machine Translation Disclaimer: This article has been translated by a computer system without human intervention. Microsoft offers these machine translations to help non-English speaking users enjoy content about Microsoft products, services and technologies. Because the article was machine translated, it may contain errors in vocabulary, syntax or grammar.

Дағдыларды жетілдіру
Оқыту курсымен танысыңыз
Жаңа мүмкіндіктерге бірінші болып қол жеткізу
Office Insider бағдарламасына қосылу

Осы ақпарат пайдалы болды ма?

Пікіріңіз үшін рақмет!

Пікіріңізге рақмет! Сізді Office қолдау көрсету қызметіндегі агенттердің бірімен байланыстырған жөн болуы мүмкін.

×