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:

  1. Click a field to select the field, click a field list to select the field list, or click the Query Design window anywhere outside a field or the field list to select the query.

  2. Select Properties on the toolbar.

  3. Modify the desired property.


If you click a field within the query design grid that has its Show check box cleared, Access displays only the query properties, not the field properties, when you bring up the Properties window for that field. If you select the Show check box with the Properties window open, Access displays the field properties.

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.


In sthe case of the Input Mask property, it is important that the Input Mask property in the query not be in conflict with the Input Mask property of the table. You can use the Input Mask property of the query to further restrict the Input Mask property of the table, but not to override it. If the query's Input Mask property conflicts with the table's Input Mask property, the user will not be able to enter data into the 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.

Figure 15.33. Query properties that affect the behavior of a given 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.

Figure 15.34. An aggregate function that retrieves the top 25% of the billable amounts.


Figure 15.35. The result of an aggregate function that shows the top 25% of the billable amounts.



You might be surprised to discover that the Top Values property doesn't always seem to accurately display the correct number of records in the query result. All records with values that match the value in the last record are returned as part of the query result. In a table with 100 records, for example, the query asks for the top 10 values. Twelve records appear in the query result if the 10th, 11th, and 12th records all have the same value in the field being used to determine the top value.

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.

Figure 15.36. A query with the Unique Values property set to No.


Figure 15.37. A query with the Unique Values property set to Yes, showing unique country/city combinations.


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.

    Part III: Creating Your Own Database and Objects
    Part V: Advanced Topics