Refining Queries with Field, Field List, and Query Properties

You can use field and query properties to refine and control the behavior and appearance of the columns in a query and of the query itself. Here's how:

Field Properties: Changing the Behavior of a Field

The properties of a field in a query include the Description, Format, Input Mask, and Caption properties of the column. The Description property documents the use of the field and controls what appears on the status bar when the user is in that column in the query result. The Format property is the same as the Format property in a table's field: It controls the display of the field in the query result. The Input Mask property, like its table counterpart, actually controls how the user enters the data and modifies it in the query result. The Caption property in the query does the same thing as a Caption property of a field: It sets the caption for the column in Datasheet view and the default label for forms and reports.

You might be wondering how the properties of the fields in a query interact with the same properties of a table. For example, how does the Caption property of a table's field interact with the Caption property of the same field in a query? All properties of a table's field are automatically inherited in queries. Properties you explicitly modify in a query override those same properties of a table's fields. Any objects based on the query inherit the properties of the query, not those of the original table.

Field List Properties: Changing the Properties of the Field List

Field list properties specify attributes of each table participating in a query. The two field list properties are Alias and Source. You most often use the Alias property when you use the same table more than once in the same query. You do this in self-joins. The Source property specifies a connection string or database name when you're dealing with external tables that aren't linked to the current database.

Query Properties: Changing the Behavior of the Overall Query

Microsoft offers many properties, shown in Figure 15.33, that allow you to affect the behavior of a query.

The Description property documents what the query does. Access 2002 introduced the Default View property. This property determines which view displays by default whenever the user runs the query. Datasheet is the default setting; PivotTable and PivotChart are the other two Default View property settings that are available. Output All Fields shows all the fields in the query results, regardless of the contents of the Show check box in each field. Top Values lets you specify the top x number or x percentage of values in the query result. You use the Unique Values and Unique Records properties to determine whether Access displays only unique values or unique records in the query's output. The following sections cover the Top Values, Unique Values, and Unique Records properties in more detail.

Several other more advanced properties exist. The Run Permissions property has to do with security. Source Database, Source Connect Str, ODBC Timeout, and Max Records all have to do with client/server issues. The Record Locks property is concerned with multiuser issues. The Recordset Type property determines whether you can make updates to the query output. By default, Access sets this property to the Dynaset type, allowing updates to the underlying data. Filter displays a subset that you determine, rather than the full result of the query. Order By determines the sort order of a query. The Orientation property determines whether the visual layout of the fields is left-to-right or right-to-left. The Subdatasheet Name property allows you to specify the name of the table or query that will appear as a subdatasheet within the current query. After you set the Subdatasheet Name property, the Link Child Fields and Link Master Fields properties designate the fields from the child and parent tables or queries that Access uses to link the current query to its subdatasheet. Finally, the Subdatasheet Height property sets the maximum height for a subdatasheet, and the Subdatasheet Expanded property determines whether the subdatasheet automatically appears in an expanded state.

The Top Values Property

The Top Values property enables you to specify a certain percentage or a specific number of records that the user wants to view in the query result. For example, you can build a query that outputs the country/city combinations with the top 10 sales amounts. You can also build a query that shows the country/city combinations whose sales rank in the top 50%. You can specify the Top Values property in a few different ways. Here are two examples:

• Click the Top Values combo box on the toolbar and choose from the predefined list of choices. (Note that this combo box is not available for certain field types.)

• Type a number or a number with a percent sign directly into the Top Values property in the Query Properties window, or select one of the predefined entries from the drop-down list for the property.

Figure 15.34 illustrates the design of a query showing the companies with the top 25% of sales. This aggregate function summarizes the result of BillableHours multiplied by BillingRate for each company. Notice that the Top Values property is set to 25%. Access has sorted the output of the query in descending order by the result of the TotalAmount calculation (see Figure 15.35). If the SaleAmount field were sorted in ascending order, Access would display the bottom 10% of the sales amount in the query result. Remember that the field(s) Access uses to determine the top values must appear as the left-most field(s) in the query's sort order.

The Unique Values Property

When it is set to Yes, the Unique Values property causes the query output to contain no duplicates for the combination of fields included in it. Figure 15.36, for example, shows a query that includes the Country and City fields from tblClients. The Unique Values property in this example is set to No, its default value. Notice that many combinations of countries and cities appear more than once. This happens whenever more than one client is found in a particular country and city. Compare this with Figure 15.37, in which the Unique Values property is set to Yes. Each combination of country and city appears only once.

The Unique Records Property

In Access 2000 and above, the default value for the Unique Records property is No. Setting it to Yes causes Access to include the DISTINCTROW statement in the SQL statement underlying the query. When set to Yes, the Unique Records property denotes that Access includes only unique rows in the recordset underlying the query in the query result—and not just unique rows based on the fields in the query result. The Unique Records property applies only to multitable queries; Access ignores it for any query that includes only one table.