Access Database – Top Value Query

Using Access database queries really control the reporting aspects of your data; asking those questions. It is the heart of any well designed database system.

There are many types of queries and one in particular can serve you well especially if you are wanting to create a league table, prioritise records or suppress the ‘lesser’ recordset.

If you want to create for example a top 10 league table using a Top Value query is the way forward.

Using a simple query and sorting by a key field will also display the answer but its not always necessary to view all the records (with or without criteria) and therefore you may want to suppress the eleventh record onwards.

With a Top Value query, you have the option to specify eiher a value or a percentage which represents a fraction of the total number of records found in the recordset and is very simple to do in Microsoft Access when building the query.

Access 2007 Top Value

You start by building your standard Select Query as normal using the Access query grid (QBE) and set your tables, fields and any criteria. Make sure you also sort by the key field which will organise your top 10 recsults.

For example, if you wanted to know the top 10 performers of sales representatives for a given month, you will need to have sales revenue field (data) sorted in descending order to award top spot to the hightest revenue first. The criteria would include a date range for a month you are querying and any other fields that support the final output for your report.

By setting the value ‘10‘ in the Return drop-down control (see illustration above) will add an additional SQL (Top) keyword to your existing query:

SELECT TOP 10 Employees......

It’s that simple!

If you want to know more about this query and other special and detailed queries within this powerful application, check out my two great value eBooks:

How to Build Access Database Queries

More Access Database Queries

Contact me if you would like to know more about me or my services.

Ben Beitler – “Your Access Database Expert!”


This entry was posted in MS Access, Queries, Reports and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.