|
|
|
|
Select All Fields in Query Design
Select All Fields in Query Design (V2 and later) An easy way to select all the fields in a table is to double-click the title bar of the field list in the upper part of the Query design window. This highlights all the fields. Then click any of the highlighted fields and drag them as a group to the Field row in the design grid. While you’re dragging, the mouse pointer changes to a multiple rectangle icon, indicating that you’re dragging multiple fields. When you release the mouse button, you’ll see that Access has copied all the fields to the design grid for you.
AND vs. OR (All versions) It’s a common mistake to get Or and And mixed up when typing a compound criteria for a single field. You may think to yourself, "I want all the entertainment groups in the states of Washington and California," and then type: WA And CA in the Criteria row for the StateOrProvince field. When you do this, you’re asking Access to find rows where (StateOrProvince = "WA") And (StateOrProvince = "CA"). Since a field in a record can’t have more than one value at a time (it can’t contain both the values "WA" and "CA" in the same record), there won’t be any records in the output. To look for all the rows for these two states, you need to ask Access to search for (StateOrProvince = "WA") Or (StateOrProvince = "CA"). In other words, type WA Or CA in the Criteria row under the StateOrProvince field.
Updatability (V2 and later) The recordset that Microsoft Access creates when you run a query looks and acts pretty much like a real table containing data. In fact, in most cases you can insert rows, delete rows, and update the information in a recordset, and Access will make the necessary changes to the underlying table or tables for you. In some cases, however, Access won’t be able to figure out what needs to be changed. Consider, for example, any calculated field. If you try to increase the amount in a Total field whose value is a result of multiplying data in the Quantity field by data in the Price field, Access can’t know whether you mean to update the Quantity field or the Price field. On the other hand, you can change either the Price field or the Quantity field and then immediately see the change reflected in the calculated Total field. In addition, Access won’t accept any change that might potentially affect many rows in the underlying table. For that reason, you can’t change any of the data in a total query or in a crosstab query. Access can’t update data in a field that has a Sum or Avg setting when the result might be based on the values in many records. When working with a recordset that is the result of a join, Access lets you update all fields from the "many" side of a join but only the nonkey fields on the "one" side, unless you have specified Cascade Update in the relationship. Also, you cannot set or change any field that has the AutoNumber data type. The ability to update fields on the "one" side of a query can produce unwanted results if you aren’t careful. For example, you could intend to assign an order to a different customer. If you change the customer name, you’ll change that name for all orders related to the current customer ID. What you should do instead is change the customer ID in the Orders table, not the customer name in the Customers table. Query Fields That Cannot Be Updated Some types of query fields cannot be updated.
|
