Share this Page URL

Chapter 9. Extending Your Use of Queries > Running Action Queries - Pg. 313

Extending Your Use of Queries SELECT CampaignExpenses.ExpenseID, CampaignExpenses.ExpenseType, CampaignExpenses.DatePurchased FROM CampaignExpenses WHERE (((CampaignExpenses.ExpenseID) In (SELECT ExpenseID FROM CampaignExpenses WHERE DatePurchased >#10/15/2003#))); 313 Notice the second SELECT statement that's part of the WHERE clause. The subquery in this case provides criteria for which expense records to select--those for which the DatePurchased field is later than 10/15/2003. Although using a nested query won't slow performance in every case, it can in some, and using a subquery might increase the speed at which a query is executed. You can create a subquery by entering the SELECT statement in the Criteria row of the query design grid. You can also open the nested query in Design view, click SQL View on the View menu, copy that query's SQL statement, and then paste the statement into the main query in its SQL window. After you insert the nested query's SQL statement, delete references to that query from the main query's SQL. Be sure to enclose the nested query's statement in parentheses. Quick Check Q. Which SQL keyword do you use to define criteria for a query? A. You use WHERE. For example, to select only records for the employee whose last name is Davolio, you would use the statement WHERE Employees.LastName = "Davolio" . Just the Steps Create a query using an SQL statement 1. 2. 3. In the Database window, double-click Create Query In Design View. Close the Show Table dialog box, and then click View, SQL View. Enter the SQL statement that defines the query: · Use SELECT to indicate which fields the query will contain. · Use FROM to identify the table the fields come from. · Use INNERJOIN in a multitable query involving tables in a one-to-many relationship to indicate which field joins the tables. · Use WHERE to define selection criteria for the query. · Use GROUP BY to sort the records. · End the SQL statement with a semicolon. 4. Close the Query window, save the query, and then run it. Running Action Queries Sometimes managing data takes place one record at a time. You need to enter a new product or a campaign expense, delete a customer who's no longer active, or increase the price of the chai you sell because you're selling so much of it these days. However, data management also requires bulk operations from time to time. You might need to adjust prices or discounts across the board, add employees from a company you just merged with, or delete all the products that haven't been prof- itable for the last six months. Action queries are designed to work on more than one record at a time. The queries you can use to modify a group of records include an update query, an append query, and a delete query. An update query changes the values in a field for a set of records. An append query adds records to a table, and a delete query removes complete records from a table. You can use a make-table query to create a new table. Action queries include different SQL keywords from those we've reviewed so far. As we create and run examples of action queries, we'll take a look at the SQL statements that Access generates for them.