Introduction to queries

• Queries help you find and work with your data

• Create a select query

• Create a parameter query

• Create a totals query

• Create a crosstab query

• Create a make table query

• Create an append query

• Create an update query

• Create a delete query

Queries help you find and work with your data

In a well-designed database, the data that you want to present through a form or report is usually located in multiple tables. A query can pull the information from various tables and assemble it for display in the form or report. A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database. Since queries are so versatile, there are many types of queries and you would create a type of query based on the task.

Create a select query

If you want to review data from only certain fields in a table, or review data from multiple tables simultaneously or maybe just see the data based on certain criteria, a select query type would be your choice. For more information, see create a simple select query.

Create a parameter query

If you frequently want to run variations of a particular query, consider using a parameter query. When you run a parameter query, the query prompts you for field values, and then uses the values that you supply to create criteria for your query.

Continuing from the previous example where you learnt to create a select query that returns orders for customers located in Las Vegas, you can modify the select query to prompt you to specify the city each time that you run the query. To follow along, open the database that you created in the previous example:

1. In the Navigation Pane, right-click the query named Orders by City (that you created in the previous section), and then click Design View on the shortcut menu.

2. In the query design grid, in the Criteria row of the City column, delete Las Vegas, and then type [For what city?].

   The string [For what city?] is your parameter prompt. The square brackets indicate that you want the query to ask for input, and the text (in this case, For what city?) is the question that the parameter prompt displays.

   Note: Neither a period (.) nor an exclamation point (!) can be used as text in a parameter prompt.

3. Select the check box in the Show row of the City column, so that the query results will display the city.

4. On the Query Design tab, in the Results group, click Run. The query prompts you to enter a value for City.

5. Type New York, and then press ENTER to see orders for customers in New York.

   What if you don't know what values you can specify? You can use wildcard characters as part of the prompt:

6. On the Home tab, in the Views group, click View, and then click Design View.

7. In the query design grid, in the Criteria row of the City column, type Like [For what city?]&"*".

   In this parameter prompt, the Like keyword, the ampersand (&), and the asterisk (*) enclosed in quotation marks allow the user to type a combination of characters, including wildcard characters, to return a variety of results. For example, if the user types *, the query returns all cities; if the user types L, the query returns all cities that start with the letter "L;" and if the user types *s*, the query returns all cities that contain the letter "s."

8. On the Query Design tab, in the Results group, click Run, and at the query prompt, type New, and press ENTER.

   The query runs, and then displays orders for customers in New York.

Create a totals query

The Total row in a datasheet is very useful, but for more complex questions, you use a totals query. A totals query is a select query that allows you to group and summarize data, like when you want to see total sales per product. In a totals query, you can use the Sum function (an aggregate function), to see total sales per product.

Use the following procedure to modify the Product Subtotals query that you created in the previous example so that it summarizes product subtotals by product.

1. On the Home tab, click View > Design View.

   The Product Subtotals query opens in Design view.

2. On the Query Design tab, in the Show/Hide group, click Totals.

   The Totals row is displayed in the query design grid.

• You can group by field values by using the Totals row in the design grid.

• You can add a datasheet Total row to the results of a totals query.

• When you use the Totals row in the design grid, you must choose an aggregate function for each field. If you do not want to perform a calculation on a field, you can group by the field.

• In the second column of the design grid, in the Total row, select Sum from the drop-down list.

• On the Query Design tab, in the Results group, click Run. The query runs, and then displays a list of products with subtotals.

• Press CTRL+S to save the query. Leave the query open.

For more information, see Display column totals in a datasheet using a Totals row.

Top of Page

Display summarized or aggregate data

When you use tables to record transactions or store regularly occurring numeric data, it is useful to be able to review that data in aggregate, such as sums or averages. In Access, you can add a Totals row to a datasheet. Total row is a row at the bottom of the datasheet that can display a running total or other aggregate value.

1. Run the Product Subtotals query you created earlier, and leave the results open in Datasheet view.

2. On the Home tab, click Totals. A new row appears at the bottom of the datasheet, with the word Total in the first column.

3. Click the cell in the last row of the datasheet named Total.

4. Click the arrow to view the available aggregate functions. Because the column contains text data, there are only two choices: None and Count.

5. Select Count. The content of the cell changes from Total to a count of the column values.

6. Click the adjoining cell (the second column). Note that an arrow appears in the cell.

7. Click the arrow, and then click Sum. The field displays a sum of the column values.

8. Leave the query open in Datasheet view.

Top of Page

Create a crosstab query

Now suppose that you want to review product subtotals, but you also want to aggregate by month, so that each row shows subtotals for a product, and each column shows product subtotals for a month. To show subtotals for a product and to show product subtotals for a month, use a crosstab query.

You can modify the Product Subtotals query again so that the query returns rows of product subtotals and columns of monthly subtotals.

1. On the Home tab, in the Views group, click View, and then click Design View.

2. In the Query Setup group, click Add Tables.

3. Double-click Orders, and then click Close.

4. On the Query Design tab, in the Query Type group, click Crosstab. In the design grid, the Show row is hidden, and the Crosstab row is displayed.

5. In the third column of the design grid, right-click the Field row, and then click Zoom on the shortcut menu. The Zoom box opens.

6. In the Zoom box, type or paste the following: Month: "Month " & DatePart("m", [Order Date])

7. Click OK.

8. In the Crosstab row, select the following values from the drop-down list: Row Heading for the first column, Value for the second column, and Column Heading for the third column.

9. On the Query Design tab, in the Results group, click Run. The query runs, and then displays product subtotals, aggregated by month.

10. Press CTRL+S to save the query.

    For more information about crosstab queries, see Make summary data easier to read by using a crosstab query.

Top of Page

Create a make table query

You can use a make-table query to create a new table from data that is stored in other tables.

For example, suppose that you want to send data for Chicago orders to a Chicago business partner who uses Access to prepare reports. Instead of sending all your order data, you want to restrict the data that you send to data specific to Chicago orders.

You can build a select query that contains Chicago order data, and then use the select query to create the new table by using the following procedure:

1. Open the example database from the previous example.

   To run a make-table query, you may need to enable the database content.

   Note: If you see a message beneath the Ribbon about enabling the database, click Enable content.If your database is already in a trusted location, you will not see the Message Bar.

2. On the Create tab, in the Query group, click Query Design.

3. Double-click Order Details and Orders.

4. In the Orders table, double-click Customer ID and Ship City to add these fields to the design grid.

5. In the Order Details table, double-click Order ID, Product ID, Quantity, Unit Price, and Discount to add these fields to the design grid.

6. In the Ship City column of the design grid, clear the box in the Show row. In the Criteria row, type 'Chicago' (include the single quotation marks). Verify the query results before you use them to create the table.

7. On the Query Design tab, in the Results group, click Run.

8. Press Ctrl + S to save the query.

9. In the Query Name box, type Chicago Orders Query, and then click OK.

10. On the Home tab, in the Views group, click View, and then click Design View.

11. On the Query Design tab, in the Query Type group, click Make Table.

12. In the Make Table dialog box, in the Table Name box, type Chicago Orders, and then click OK.

13. On the Query Design tab, in the Results group, click Run.

14. In the confirmation dialog box, click Yes, and see the new table displayed in the Navigation Pane.

    Note: If there is already a table with the same name that you specified, Access deletes that table before running the query.

For more information about using make table queries, see Create a make table query.

Top of Page

Create an append query

You can use an append query to retrieve data from one or more tables and add that data to another table.

For example, suppose that you created a table to share with a Chicago business associate, but you realize that the associate also works with clients in the Milwaukee area. You want to add rows that contain Milwaukee area data to the table before you share the table with your associate. You can add Milwaukee area data to the Chicago Orders table by using the following procedure:

1. Open the query named "Chicago Orders Query" you created earlier in Design view.

2. On the Query Design tab, in the Query Type group, click Append. The Append dialog box opens.

3. In the Append dialog box, click the arrow in the Table Name box, select Chicago Orders from the drop-down list, and then click OK.

4. In the design grid, in the Criteria row of the Ship City column, delete 'Chicago', and then type 'Milwaukee'.

5. In the Append To row, select the appropriate field for each column.

   In this example, the Append To row values should match the Field row values, but that is not required for append queries to work.

6. On the Query Design tab, in the Results group, click Run.

For more information about append queries, see Add records to a table by using an append query.

Top of Page

Create an update query

You can use an update query to change the data in your tables, and you can use an update query to enter criteria to specify which rows should be updated. An update query provides you an opportunity to review the updated data before you perform the update.

In the previous example, you appended rows to the Chicago Orders table. In the Chicago Orders table, the Product ID field shows the numeric Product ID. To make the data more useful in reports, you can replace the product IDs with product names, use the following procedure:

1. Open the Chicago Orders table in Design view.

2. In the Product ID row, change the Data Type from Number to Text.

3. Save and close the Chicago Orders table.

4. On the Create tab, in the Query group, click Query Design.

5. Double-click Chicago Orders and Products.

6. On the Query Design tab, in the Query Type group, click Update.

7. In the design grid, the Sort and Show rows disappear, and the Update To row appears.

8. In the Chicago Orders table, double-click Product ID to add this field to the design grid.

9. In the design grid, in the Update To row of the Product ID column, type or paste the following: [Products].[Product Name]

   Tip: You can use an update query to delete field values by using an empty string ("") or NULL in the Update To row.

10. In the Criteria row, type or paste the following: [Product ID] Like ([Products].[ID])

11. You can review which values will be changed by an update query by viewing the query in Datasheet view.

12. On the Design tab, click View > Datasheet View. The query returns a list of Product IDs that will be updated.

13. On the Query Design tab, click Run.

    When you open the Chicago Orders table, you will see that the numeric values in the Product ID field have been replaced by the product names from the Products table.

For more information about update queries, see Create and run an update query.

Top of Page

Create a delete query

You can use a delete query to delete data from your tables, and you can use a delete query to enter criteria to specify which rows should be deleted. A delete query provides you an opportunity to review the rows that will be deleted before you perform the deletion.

For example, say that while you were preparing to send the Chicago Orders table from the previous example, to your Chicago business associate, you notice that some of the rows contain a number of empty fields. You decided to remove these rows before you send the table. You could just open the table and delete the rows manually, but if you have many rows to delete and you have clear criteria for which rows should be deleted, you might find it helpful to use a delete query.

You can use a query to delete rows in the Chicago Orders table that do not have a value for Order ID by using the following procedure:

1. On the Create tab, click Query Design.

2. Double-click Chicago Orders.

3. On the Query Design tab, in the Query Type group, click Delete. In the design grid, the Sort and Show rows disappear, and the Delete row appears.

4. In the Chicago Orders table, double-click Order ID to add it to the grid.

5. In the design grid, in the Criteria row of the Order ID column, type Is Null.

6. On the Query Design tab, in the Results group, click Run.

For more information about delete queries, see Create and run a delete query.

Top of Page