MeadInKent
| Feedback | Conditional formatting of data | Things to do with Queries |

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
  query source
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

 
UK more ...   USA 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
  source table
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


file: Acc-TopTen.htm Page last modified Mar14 MeadInKent.co.uk 2014