Customizing Query Properties

Access 2007 provides a number of properties associated with queries that you can use to control how a query runs. To open the property sheet for queries, click in the upper part of a Query window in Design view outside of the field lists and then click the Property Sheet button in the Show/Hide group of the Design contextual tab. Figure 8–41 shows the property sheet Access provides for select queries.

Figure 8–41: The property sheet for select queries lets you customize the way the query works.

Use the Description property to document what the query does. This description appears next to the query name when you view query objects in Details view in the Navigation Pane. You can also right-click on the query in the Navigation Pane and open the Properties dialog box to enter this property without having to open the query in Design view.

The Default View property determines how the query opens when you open it from the Navigation Pane. Datasheet view is the default, but you might want to change this setting to PivotTable or PivotChart if you have designed either of these views for the query. See “Creating PivotTables and PivotCharts from Queries,” page 469, for details.

Controlling Query Output

You normally select only specific fields that you want returned in the recordset when you run a select query. However, if you’re designing the query to be used in a form and you want all fields from all tables used in the query available to the form, set the Output All Fields property to Yes. It’s a good idea to keep the default setting of No and change this option only for specific queries.

Inside Out-Don’t Change the Default Setting for the Output All Fields Property 

You can change the default Output All Fields property for all queries in the Object Designers category of the Access Options dialog box, but we strongly recommend that you do not do this. Queries execute most efficiently when they return only the fields that you need. Also, when your query includes more than one table and a field exists more than once in different tables, an expression you use to reference the field will fail unless you qualify the field name with the table name. You might include the field only once in the design grid, but Output All Fields causes the query to include all copies of the field.

You can use the Top Values property to tell Access that you want to see the first ,n rows or the first x% of rows. If you enter an integer value, Access displays the number of rows specified. If you enter a decimal value between 0 and 1 or an integer less than 100 followed by a percent sign (%), Access displays that percentage of rows. For example, you might want to find the top 10 best-selling products or the top 20% of highest paid employees. Note that in most cases you’ll need to specify sorting criteria-perhaps by count of products sold descending or salary descending-to place the rows you want at the “top” of the recordset. You can then ask for the top 10 or top 20% to get the answers you want.

When working in a query datasheet, you can define and apply filters and specify sorting just as you can in a table datasheet. Access stores this filtering and sorting criteria in the query’s Filter and Order By properties. When you design a query, you can use the Filter and Order By properties to predefine filtering and sorting criteria. When you open the query and click Toggle Filter in the Sort & Filter group on Home tab, Access applies the filter and/or sorts the data using these saved properties. If you change the filter or sorting criteria while in Datasheet view and then save the change, Access updates these properties.

You can also affect whether the fields returned by the query can be updated by changing the Recordset Type property. The default setting, Dynaset, allows you to update any fields on the many side of a join. It also lets you change values on the one side of a join if you have defined a relationship between the tables and enabled Cascade Update Related Fields in the Edit Relationships dialog box. If you choose Dynaset (Inconsistent Updates), you can update any field that isn’t a result of a calculation, but you might update data that you didn’t intend to be updatable. If you want the query to be readonly (no fields can be updated), choose the Snapshot setting.

Caution 

You should rarely, if at all, choose the Dynaset (Inconsistent Updates) setting for Recordset Type. This setting makes fields updatable in queries that might not otherwise allow updating. Although Access still enforces referential integrity rules, you can make changes to tables independently from each other, so you might end up reassigning relationships unintentionally. You can read about the details of when fields are updatable in a query later in this chapter in “Limitations on Using Select Queries to Update Data” on page 468.

Working with Unique Records and Values

When you run a query, Access often returns what appear to be duplicate rows in the recordset. The default in Access 2007 is to return all records. You can also ask Access to return only unique records. (This was the default for all versions of Access prior to version 8.0, also called Access 97). Unique records mean that the identifier for each row (the primary key of the table in a single-table query or the concatenated primary keys in a multiple-table query) is unique. If you ask for unique records, Access returns only rows with identifiers that are different from each other. If you want to see all possible data (including duplicate rows), set both the Unique Values property and the Unique Records property to No. (You cannot set both Unique Records and Unique Values to Yes. You can set them both to No.)

To understand how the Unique Values and Unique Records settings work, open the ContactsDataCopy.accdb database and create a query that includes both the tblContacts table and the tblContactEvents table. Let’s say you want to find out from which cities you’ve received a contact over a particular period of time. Include the WorkCity and WorkStateOrProvince fields from tblContacts. Include the ContactDateTime field from tblContactEvents, but clear the Show check box. Figure 8–42 shows a sample query with a date criterion that will show contact cities between December 2006 and March 2007. (Remember, ContactDateTime includes a time value, so you need to enter a criterion one day beyond the date range you want.) You can find this query saved as qxmplNoUnique in the sample database.

Figure 8–42: You can build a query that demonstrates setting both Unique Values and Unique Records to No when you’re using two tables.

If you switch to Datasheet view, as shown in Figure 8–43, you can see that the query returns 58 rows-each row from tblContacts appears once for each related contact event that has a contact date between the specified days. Some of these rows come from the same person, and some come from different people in the same city. The bottom line is there are 58 rows in tblContactEvents within the specified date range.

Figure 8–43: Run your sample query to see the result of retrieving all rows across a join even though the output columns are from only one of the tables.

If you’re interested only in one row per contact (per person) from tblContacts, regardless of the number of contact events, you can set the Unique Records property to Yes. The result is shown in Figure 8–44 (saved as qxmplUniqueRecords). This tells us that there were 26 different people who had a contact event within the date range. Again, some of these rows come from different people in the same city, which is why you see the same city listed more than once. The recordset now returns unique records from tblContacts (the only table providing output fields in this query).

Note 

Setting Unique Records to Yes has no effect unless you include more than one table in your query and you include fields from the table on the one side of a one-to-many relationship. You might have this situation when you are interested in data from one table but you want to filter it based on data in a related table without displaying the fields from the related table.

Figure 8–44: Even though your query uses two tables, when you set the Unique Records property to Yes, your query returns records that are unique in the one table that provides output columns.

Finally, if you’re interested in only which distinct cities you received a contact from in the specified date range, and you want to see each city name only once, then set Unique Values to Yes. (Access automatically resets Unique Records to No.) The result is 13 records, as shown in Figure 8–45 (saved as qxmplUniqueValues).

Figure 8–45: When you set the Unique Values property to Yes, Access removes all the duplicate records.

When you ask for unique values, you’re asking Access to calculate and remove the duplicate values. As with any calculated value in a query, fields in a unique values query can’t be updated.

Defining a Subdatasheet

In the previous chapter, we showed you how to work with and modify subdatasheets from query Datasheet view. Now, let’s take a closer look at the properties you can set in a query to predefine a subdatasheet within the query. Let’s say you want to create a query to show company information and make a subdatasheet available that displays information about the primary contact for the company. You can use the qryContactsDatasheet query that you studied in the previous chapter, but first you’ll need to modify that query for this exercise.

In the ContactsDataCopy.accdb database, open qryContactsDatasheet in Design view. To link this query to another that displays company information, you’ll need the CompanyID field. Click the Show Table button in the Query Setup group of the Design contextual tab, and add the tblCompanyContacts table to the query. Click Close to close the Show Table dialog box. You should see a join line linking ContactID in the two tables. Add the CompanyID and DefaultForContact fields from tblCompanyContacts to the first two columns in the design grid. It makes sense to list only the default company for each contact, so add a criterion of True on the Criteria line under the DefaultForContact field. You should clear the Show check box because you don’t really need to see this field in the output. Your query should now look like Figure 8–46. Click the Microsoft Office Button, click Save As, click Save Object As, and then save the query as qryContactsDatasheetCOID. You can also find this query saved in the sample database as qryXmplContactsDatasheetCOID.

Figure 8–46: To modify a query to use it as a subdatasheet that displays contacts, include the appropriate linking field.

You need the CompanyID field to provide a link to the outer datasheet that you will build shortly, but you don’t necessarily need to see it in your datasheet. Switch to Datasheet view, click in the Company/Org column (this is the CompanyID field), click the More command in the Records group on the Home tab, and select Hide Columns from the list to hide the field. Note that this affects the display only. CompanyID is still a field in the query. (Alternatively, you could clear the Show check box in the design grid.) Close the query and click Yes when Access asks you if you want to save your layout changes.

Now you’re ready to build the query in which you’ll use the query you just modified as a subdatasheet. Start a new query, add the tblCompanies table to the design grid, and close the Show Tables dialog box. Include the CompanyID, CompanyName, City, and StateOrProvince fields from tblCompanies. Click in the blank space in the top part of the Query window, and click the Property Sheet button in the Show/Hide group on the Design tab to open the property sheet for the query. Click in the Subdatasheet Name property and then click the small arrow to open a list of all tables and queries saved in your database. Scroll down and choose the query that you just saved-qryContactsDatasheetCOID-and select it as shown in Figure 8–47. (You might have to widen the property sheet in order to see the complete names of queries.)

Figure 8–47: Select the qryContactsDatasheetCOID query to provide the subdatasheet for this query.

You need to tell your query which field links the query you’re creating to the query in the subdatasheet. In Link Child Fields, type the name of the field in the subdatasheet query you just selected that matches a field in the query you’re designing-in this case, CompanyID. Note that when you’re linking to another table or query that requires more than one field to define the relationship, you enter the field names separated by semicolons. In Link Master Fields, enter the name of the field in the query you’re creating that should match the value in the field in Link Child Fields-CompanyID again. (You’ll see these same properties again when inserting a subform in a form in Chapter 13, “Advanced Form Design.”)

Two additional properties apply to subdatasheets-Subdatasheet Height and Subdatasheet Expanded. If you leave Subdatasheet Height at its default setting of 0, the subdatasheet area expands to show all available rows when you open it. You can limit the maximum height by entering a setting in inches (centimeters on a computer with regional settings set to the metric system). If the height you specify isn’t large enough to show all rows, you’ll see a scroll bar to move through the rows in the subdatasheet. If you set Subdatasheet Expanded to Yes, the subdatasheet for every row opens expanded when you open the query or switch to Datasheet view. Just for fun, change this setting to Yes.

Inside Out-Cycling Through Property Box Values 

When a property box has a list of values from which you can select, you can cycle through the values by double-clicking in the property value box instead of clicking the small arrow to the right of the property box.

Switch to Datasheet view, and your result should look like Figure 8–48.

Figure 8–48: This query shows company information with its contact subdatasheet information expanded.

Do you notice that there are little plus signs on the rows in the subdatasheet? Remember that qryContactsDatasheet, from which you built the new qryContactsDatasheetCOID query, also has a subdatasheet defined. If you click on one of the plus signs in the subdatasheet, you’ll see the related contact event subdatasheet information from qryContactsDatasheet as shown in Figure 8–49. You can actually nest subdatasheets like this up to seven levels.

Figure 8–49: You can expand the subdatasheet of the subdatasheet to see contact event information.

You can find this query saved as qxmplCompaniesContactsSub in the sample database.

Other Query Properties

Use the Record Locks property to control the level of editing integrity for a query that is designed to access data shared across a network. The default is No Locks-to not lock any records when the user opens the query. With this setting, Access applies a lock temporarily only when it needs to write a row back to the source table. Select the Edited Record setting to lock a row as soon as a user begins entering changes in that row. The most restrictive setting, All Records, locks every record retrieved by the query as long as the user has the query open. Use this setting only when the query must perform multiple updates to a table and other users should not access any data in the table until the query is finished.

Four of the remaining properties-Source Database, Source Connect Str, ODBC Timeout, and Max Records-apply to dynamically linked tables. You can, for example, run a query against tables in another Access database by entering the full path and file name of that database in Source Database. Access dynamically links to the database when you run the query. Use Source Connect Str when you are dynamically linking to an ODBC or non-Access database that requires connection information. ODBC Timeout specifies how long Access will wait (in seconds) for a response from the ODBC database before failing with an error. Use Max Records to limit the number of rows returned to this query from a database server. When you don’t specify a number, Access fetches all rows. This might not be desirable when you’re fetching data from tables that have hundreds of thousands of rows.

The last property, Orientation, specifies whether you want to see the columns in Leftto-Right or Right-to-Left order. In some languages where reading proceeds right to left, this setting is handy. You can try it out on your system. You’ll find that the columns show up right to left, the caption of the query is right-aligned, the selector bar is down the right side of the datasheet, and pressing the Tab key moves through fields from right to left.