A 'top values' query
Access queries can be instructed to display a partial subset of the records
in a table.
This could be the top 10 records (when sorted by a field value) or the lowest 5%. This is achieved
by modifying the 'Top Values' property of a query.
The following example reads a Bookings table and displays the three most overdue invoices.
| WorstDebtors: Query |
ox |
| 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) |
ox |
| |
 |
| 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, use the ribbon
option Query Tools Design | Query Setup | Return. Alternatively click somewhere
within the grey tables area in the top half of the design window and select
Query Properties. The Top Values property can be set to any percentage or value,
not just the selection of values which are displayed 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 |
Access 2010 Step by Step |
|
|
|
|
 |
more ... |
|
 |
more ... |
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) |
ox |
| |
|
| 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 |
