They allow you to perform a wide variety of very powerful analysis and actions. They can be chained together one query uses another query , and are used throughout MS Access forms and reports. There are many types of queries. Most users are familiar with queries that generate results: By default, Access Select queries are updateable editable. When you view the datasheet of a Select query, you can edit the results and your changes are saved in the underlying table.
This works even when the query returns records from more than one table. Unfortunately, there are situations where the query results are not updateable. This Recordset is not updateable When you try to edit the query, this message appears in the status bar: Operation must use an updateable query Reasons why a Query or Recordset is not Updateable There are many reasons why your data may not be updateable.
Some are pretty obvious: The table being modified is a linked table without a primary key. For certain backend databases e. This makes sense since Access wants to issue a SQL query for modifications but can't uniquely identify the record.
Less obvious are these situations: Queries with some summary fields linked to individual records and the individual records still can't be edited Queries with multi-table joins that are not on key fields Union queries The most obvious and annoying situations are queries on one table with totals Group By, Sum, Count, Average, etc.
However, if any portion of your query contains a summary GROUP BY field, all the fields of the returned records are not editable not just the summary fields. This paper discusses some ways around this limitation. Editing a Table with a Summary Field For example, a query listing all the customers ranked by how much they purchased: The first query provides the sales summary for each customer: Total Sales by Customer The second query uses the first query to show the customers: However, it is not updateable.
Because there is a summary in the query, the entire query and all its fields are not updateable. Updating a Field with a Summary Query Similarly, if your Customer table has a field to store summarized information, an Update Query should do the trick: Attempt to update field with data from a Summary query Operation must use an updateable query Error If you run this query, you get this error message: Operation must use an updateable query This is very annoying.
One way to resolve this is to use module code to open two recordsets: A better way is to use temporary tables. Use a Temporary Table By using temporary tables, you can eliminate a lot of unnecessary code. Make Table Queries Make-Table queries are just like Select queries except their results are put into a new table rather than a datasheet view. You specify the table name and it is created.
If the table exists, it is replaced. A dialog lets you specify the name of the output table: Make Table Query puts its results in table: Append queries let you insert records from a query into an existing table. If you just have a one step process, there usually is not much difference. However, if you have multiple steps, Append queries have a clear advantage. Append queries let you create the table in advance with specific field names and field types.
Make-Table queries assigns its own field types for expressions, which may be a problem for the text width of text fields and number fields. This is particularly important if you need to use those fields to link to other tables. An Append query can use a table with indexes and other field formatting that a Make Table query cannot specify.
Append queries can be used to insert records into a table with fields not in the query. The fields may be updated later with other queries. Append queries let you accumulate results from multiple queries in one table. For these reasons, you should become familiar with Append queries. Follow these steps to setup your application to use Append queries: Create the target table. I usually use a Make-Table query to do this, then manually designate a key field, modify the field names, types, settings, and descriptions.
Convert your Make-Table query to an Append query. When designing the query, select Append Query from the Access Query menu. In the query grid, specify the fields in the "Append To: Notice how the Append To field names do not need to match the field names or expressions in the query: Notice how field Expr1 goes into the nicely named "Sales" field Create forms, reports, or other analysis based on the data in the target table.