Categories
Blog

SELECT TOP and SELECT TOP PERCENT Queries

With the vast number of possible queries you can make it may be difficult to determine exactly what it is you need and when you need it. In this article we will take a look at two queries that become increasingly helpful (and powerful) as your database grows. When you are faced with ploughing through hundreds, thousands, or millions of lines of data, the SELECT TOP and SELECT TOP PERCENT queries

give you a way to comb through all of the data in a matter of seconds. Using either of these queries does require some knowledge of SQL, so this article is for more advanced users. If you are just starting out with your database, this article will not be very helpful to you right now. Bookmark it and return later when you are more comfortable with SQL and when the amount of data you have to deal with requires more than just a simple sort or filter can offer.

A precursory look at the names of the queries may make it seem as though they do the same thing, but they are both very specific as to how they are used. They are both universal to all databases, but this article focuses on how they are used in Microsoft Access. If you are at least familiar with SQL, you will be able to use the code for other databases.  

When to Use SELECT TOP

SELECT TOP is a way to look for a specific number of files that will be returned, such as the top 10 repeat clients, top 20 client sales, or top 10 countries for

sales. It is the quickest and easiest way to narrow your search to just the best (or possible worst) of any particular scenario. When you don’t have much data you aren’t going to have much use for this query, but as your databases grow, it becomes a requirement to quickly pull the data you need to concentrate on.

The reason running Select Top is better than trying to run a sort or filter is that it allows you to look at only the data that meets your criteria. You won’t have to copy/paste information from the database into a spreadsheet or anything else to exclude irrelevant information.

When to Use SELECT TOP PERCENT

SELECT TOP PERCENT performs a calculation before returning the data you want. In other words, it does more than just look at a number to determine if it is in the top X of all of the data; the query performs calculations to determine percentages.

For example, if you have a customer who buys $1,000 worth of your product at one time and another who is a repeat customer who buys $250 worth of your product every month, they will appear differently in the queries. Since $1,000 is considerably more money, it is more likely to appear in a list of top items, but with the regular customers they are much more likely to appear in a search for top 10% of top buying customers.

Executing SELECT TOP in Microsoft Access

While it is possible to create your own query from scratch, Access does make it easy to pick and choose what you need, and the program will fill in most of the required elements. The following will create the top 5 records. 

  1. Create a new query by clicking on Create > Query Design.
  2. Select the table or tables you want the query to run against.
  3. On the Property Sheet to the right, find the Top Values option and change it to the desired top fields for that column. The default values for SELECT TOP are 5, 25, and 100. If you know SQL, you can go into the query and update it to a value that is not part of the default.
  4. Open the query you want to change.
  5. Click on Query Tools > Design > View > SQL View. A window opens and you can adjust SELECT TOP # to make # any number you want.
  6. Save the change.

Executing SELECT TOP PERCENT in Microsoft Access

While it is possible to create your own query from scratch, Access does make it easy to pick and choose what you need, and the program will fill in most of the required elements.

  1. Create a new query by clicking on Create > Query Design.
  2. Select the table or tables you want the query to run against.
  3. On the Property Sheet to the right, find the Top Values option and change it to the desired top fields for that column. The default values for SELECT TOP PERCENT are 5% and 25%. If you know SQL, you can go into the query and update it to a value that is not part of the default.
  4. Open the query you want to change.
  5. Click on Query Tools > Design > View > SQL View. A window opens and you can adjust SELECT TOP # PERCENT to make # any number you want.
  6. Save the change.

Keep in mind that your queries will be tied to tables, so you need to be reviewing the tables themselves (not reports or forms). 

Practicing

While the process seems straightforward, it generally takes a bit of time to get accustomed to making your own adjustments. It is best to start with a sandbox version of a database instead of working with a live database. That will ensure that you don’t inadvertently change a query that someone uses or create any problems for yourself and others down the road.

Leave a Reply

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