لطفا منتظر باشید ...
Special Query Properties
Access 2003 queries have several properties that can dramatically change their behavior. To look up a query's properties, right-click on a blank area in the top half of the Query window and select Properties to open the Properties window (see Chapter 4 discusses many of these properties. The following sections cover the Unique Values, Unique Records, and Top Values properties.
Figure 11.15. Viewing the general properties for a query.
Unique Values Property
When set to Yes, the Unique Values property causes the query output to contain no duplicates for the combination of fields you include in it. Figure 11.16, 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 11.17, in which the Unique Values property is set to Yes. Each combination of country and city appears only once.
Figure 11.16. A query with the Unique Values property set to No.
Figure 11.17. A query with the Unique Values property set to Yes.
Unique Records Property
In Access 2000 and later, the default value for the Unique Records property is No. Setting it to Yes causes the DISTINCTROW statement to be included in the SQL statement underlying the query. When set to Yes, the Unique Records property denotes that Jet includes only unique rows in the recordset underlying the query in the query resultand not just unique rows based on the fields in the query result. The Unique Records property applies only to multitable queries; Jet ignores it for queries that include only one table.
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 (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 11.18 illustrates the design of a query showing the companies with the top 25% of sales. This Total query summarizes the result of the BillableHours multiplied by the BillingRate for each company. Notice that the Top Values property is set to 25%. The output of the query is sorted in descending order by the result of the BillableAmount calculation (see Figure 11.19). If the SaleAmount field were sorted in ascending order, the bottom 10% of the sales amount would be displayed in the query result. Remember that the field(s) you want to use to determine the top values must appear as the left-most field(s) in the query's sort order.
Figure 11.18. A Total query that retrieves the top 25% of the billable amounts.
Figure 11.19. The result of a Total query showing the top 25% of the billable amounts.
NOTEYou 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. Jet returns all records with values that match the value in the last record as part of the query result. In a table with 100 records, for example, the query asks for the top 10 values. Twelve records will 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.
