When you want to review, add, change, or delete data from your database consider using a query.
Using a query, you can answer very specific questions about your data that would be difficult to answer by looking at table data directly. You can use queries to filter your data, to perform calculations with your data, and to summarize your data. You can also use queries to automate many data management tasks and to review changes in your data before you commit to those changes.
Note Aggregate query functions, such as Sum or Count, are not available in Web queries.
This article provides basic information about queries and provides examples of the different types of queries.
A query is a request for data results, for action on data, or for both. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data. Queries that you use to retrieve data from a table or to make calculations are called select queries. Queries that add, change, or delete data are called action queries.
You can also use a query to supply data for a form or report. In a well-designed database, the data that you want to present by using a form or report is often located in several different tables. By using a query, you can assemble the data that you want to use before you design your form or report.
For more information about using a query to supply data for forms and reports, refer to the links in the See Also section, or search Help.
If you are not familiar with the Navigation Pane, you may not know how to open a query that already exists. The Navigation Pane is a feature that replaces the Database Window from versions prior to Access 2007. You'll see the Navigation Pane along the left side of your screen.
To open any database object, including a query, you can double-click the object in the Navigation Pane.
Note Examples in this article use a database that was created by using the Northwind 2010 database template.
Sometimes you may want to review all of the data from a table, but at other times, you may want to review only the data from certain fields, or you may want to review data only if certain fields meet certain criteria. To review some of the data in a table, you use a select query.
Suppose that you want to review a list of products and their prices. You can create a query that returns product and price information by using the following procedure:
The query runs, and then displays a list of products and their prices.
Tables in a well-designed database bear logical relationships (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.) to each other. These relationships exist on the basis of fields that the tables have in common. When you want to review data from related tables, you use a select query.
Suppose that you want to review orders for customers who live in a particular city. Data about orders and data about customers are stored in two tables in the same database. Each table has a Customer ID field, which forms the basis of 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.) between the two tables. You can create a query that returns orders for customers in a particular city, for example, Las Vegas, by using the following procedure:
Note the line, called a join, that connects the ID field in the Customers table and the Customer ID field in the Orders table. This line shows the relationship between the two tables.
Clearing the Show check box prevents the query from displaying the city in its results, and typing Las Vegas in the Criteria row specifies that you want to see only records where the value of the City field is Las Vegas. In this case, the query returns only the customers that are located in Las Vegas — Company L and Company AA.
Note that you do not have to display a field to use it with a criterion.
The query runs, and then displays a list of orders for customers in Las Vegas.
The Save As dialog box appears.
Sometimes you may want to run a query that is only slightly different from an existing query. You can change the original query to use your new criteria, but 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.
In the previous example, you created a query that returns orders for customers who are located in Las Vegas. You can modify the query to prompt you to specify the city each time that you run the query by using the following procedure:
Note This step is not necessary if the Navigation Pane is displayed.
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.
The query prompts you to enter a value for City.
The query runs, and then displays orders for customers in New York.
But what if you don't know what values you can specify? To make your parameter more flexible, you can use wildcard characters as part of the prompt:
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."
At the query prompt, type New, and then press ENTER.
You can also specify what type of data a parameter should accept. You can set the data type for any parameter, but it is especially important to set the data type for numeric, currency, or date/time data. When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected.
Note If a parameter is set to accept text data, any input is interpreted as text, and no error message is displayed.
To specify the data type for parameters in a query, use the following procedure:
Most of the time, you do not use tables to store calculated values that are based on data in the same database. For example, the Order Details table in Northwind 2007 does not store product subtotals, because the subtotal for any given product is calculated by using data that is stored in the Quantity, Unit Price, and Discount fields of the Order Details table.
In some cases, calculated values can become out-of-date, because the values that they are based on change. For example, you would not want to store someone's age in a table, because every year you would have to update the value; instead, you store the person's date of birth, and then use an expression in a query to calculate the person's age.
Subtotal: ([Quantity]*[Unit Price])-([Quantity]*[Unit Price]*[Discount])
This is the calculated field. The calculated field multiplies the quantity of each product by the unit price for that product, multiplies the quantity of each product by the unit price and discount for that product, and then subtracts the total discount from the total unit price.
The query runs, and then displays a list of products and subtotals, per order.
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.
Access allows you to review simple aggregate data in any datasheet (datasheet: Data from a table, form, query, view, or stored procedure that is displayed in a row-and-column format.) by adding a Total row. A Total row is a row at the bottom of the datasheet that can display a running total or other aggregate value.
Note You cannot add a Total row to the datasheet of a Web query.
You can add a Total row to the Product Subtotals query that you created in the previous example by using the following procedure:
A new row appears at the bottom of the datasheet, with the word Total in the first column.
Note that an arrow appears in the cell.
Because the column contains text data, there are only two choices: None and Count.
The content of the cell changes from Total to a count of the column values.
Note that an arrow appears in the cell.
The field displays a sum of the column values.
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. For example, suppose that you want to see total sales per product. In a totals query, you can use Sum, an aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.), to see total sales per product.
Note You cannot use aggregate functions in a Web query.
Use the following procedure to modify the Product Subtotals query to make it summarize product subtotals by product.
The Product Subtotals query opens in Design view.
The Totals row is displayed in the query design grid.
Note Although they have similar names, the Totals row in the design grid and the Total row in a datasheet are not the same:
The query runs, and then displays a list of products with subtotals.
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 (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.).
Note A crosstab query cannot be displayed in a web browser.
You can modify the Product Subtotals query again so that the query returns rows of product subtotals and columns of monthly subtotals.
In the design grid, the Show row is hidden, and the Crosstab row is displayed.
Month: "Month " & DatePart("m", [Order Date])
The query runs, and then displays product subtotals, aggregated by month.
You can use a make-table query to create a new table from data that is stored in other tables.
Note A make-table query cannot be run in a web browser.
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.
Note If your database is in a trusted location, the Message Bar does not appear and enabling the content is unnecessary.
Verify the query results before you use them to create the table.
The Save As dialog box appears.
The Make Table dialog box appears.
The new table is created, and the table appears in the Navigation Pane.
Note If there is already a table that has the name that you specified, that table is deleted before the query runs.
Because the Chicago Orders table exists, a warning dialog box appears.
You can use an append query to retrieve data from one or more tables and add that data to another table.
Note An append query cannot be run in a web browser.
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 that you created in the previous example by using the following procedure:
The Append dialog box closes. In the design grid, the Show row disappears, and the Append To row appears.
In this example, the Append To row values should match the Field row values, but that is not required for append queries to work.
Note While running a query that returns a large amount of data you might get an error message indicating that you will not be able to undo the query. Try increasing the limit on the memory segment to 3MB to allow the query to go through.
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.
Important An action query cannot be undone. You should consider making a backup of any tables that you will update by using an update query.
Note An update query cannot be run in a web browser.
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 for reports, you may want to replace the product IDs with product names. To replace the product IDs, you must first change the data type of the Product ID field of the Chicago Orders table from Number to Text, so that the Product ID field can accept product names.
You can update the values in the Chicago Orders table by using the following procedure:
In the design grid, the Sort and Show rows disappear, and the Update To row appears.
Tip You can use an update query to delete field values by using an empty string ("") or NULL in the Update To row.
[Product ID] Like ([Products].[ID])
The Like keyword is necessary because the fields that you are comparing contain different data types (Product ID is text data, ID is numeric data).
On the Design tab, in the Results group, click View, and then click Datasheet View.
The query returns a list of Product IDs that will be updated.
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.
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.
Note A delete query cannot be run in a web browser.
Suppose that you are preparing to send the Chicago Orders table (updated in the previous example) to your Chicago business associate, and you notice that some of the rows contain a number of empty fields. You would like to remove these rows that contain empty fields before you send the table. You could just open the table and delete the rows manually, but you might find it helpful to use a delete query if there are more than a few rows that you want to delete and you have clear criteria for which rows should be deleted.
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:
In the design grid, the Sort and Show rows disappear, and the Delete row appears.