A 'top values' query
Access queries can be instructed to display only a limited number of records.
This could be the top 10 occurrences of a value or the lowest 5%. This achieved
by modifying the 'Top Values' property of a query.
The following example shows the three most overdue invoices.
| WorstDebtors:
Query |
| User Group |
Inv Raised |
Invoice £ |
DaysOvrDue |
| SUBSTANTIAL THEATRE GROUP |
14 Apr 05 |
£123 |
255 |
| MR AND MRS JONES |
08 Sep 05 |
£26 |
108 |
| CODELINK |
23 Oct 05 |
£38 |
63 |
The example uses a Select Query with a criteria to select only those items
with no invoice payment date. The number of days since the invoice date is
calculated and sorted in descending order.
| WorstDebtors:
Query (Design) |
| |
 |
| Field: |
Invoice_Date |
Invoice_Amount |
DaysOvrDue: Date()-[Invoice_Date] |
Date_InvPaid |
| Table: |
Bookings |
Bookings |
|
Bookings |
| Sort: |
|
|
Descending |
|
| Show: |
Y |
Y |
Y |
N |
| Criteria: |
|
|
|
Is Null |
| Or: |
|
|
|
|
| |
To amend a query so that it only displays the top values, click somewhere
within the grey tables area in the top half of the design window, then select
Query Properties. The Top Values property can be set to any percentage or value,
not just the selection of values which are contained in the drop down list. The
property is associated with the whole Query, not with those of a particular
field. The
field which is to contain the top values must be the one which by which the
query is sorted. If you want to change from the lowest to the highest values,
switch the sort order from Ascending to Descending.
| Query
Properties |
| General |
|
| Description .... |
|
| Output All Fields ... |
No |
| Top Values ....... |
3 |
| Unique Values .... |
No |
In this second example it is intended to focus a promotional offer on the 50% of customers
that have spent the most
on a product in the last year. This query is grouped by customer in order to sum the total
value of their invoices.
| BestCustomers:
Query (Design) |
| |
|
| Field: |
UserGroup_Name |
Invoice_Amount |
ItemsCount: 1 |
|
| Table: |
Bookings |
Bookings |
|
|
| Total: |
Group By |
Sum |
Sum |
|
| Sort: |
|
Descending |
|
|
| Show: |
Y |
Y |
Y |
|
| Criteria: |
|
|
|
|
| |
The query Top Value property has this time been set as follows and will
result in 4 of the 8 customers being displayed, together with the value and the number of transactions.
| Query
Properties |
| General |
|
| Description .... |
|
| Output All Fields ... |
No |
| Top Values ....... |
50% |
| Unique Values .... |
No |
