TOP N RECORDS IN QUERY ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We have seen the usage of different types of complicated Queries like the following:

Today, we will learn how to define and extract the top 100 records or a certain percentage of the total records based on the values in a particular Column.

Review of Rules of Queries.

You need to know only a few rules to work with this type of Query.

You can select several Columns of data from the source for output.
You must sort one or more columns of data in ascending or descending Order, and the leftmost sorted column will pick the top valuation records.
If the output contains duplicate records (i.e., two or more records with identical values in all columns), you can set the Unique Values property to Yes, which is equivalent to using a DISTINCT clause in the SQL SELECT statement, to suppress the duplicates.
If the query has more than one Table/Query as the source, and when duplicate records are found in the output, set the Unique Records Property to Yes (DISTINCTROW clause in the SELECT statement) to suppress duplicate records.

Create a Sample Query.

Create a new database or one of your existing databases.
Import the Order Details and Products Table from the Northwind.mdb sample Database. The Products table is not directly used, but there is a lookup reference to this table in the Order Details table for Product Name.
Open a new Query in SQL View (without selecting a Table/Query from the displayed list).

Copy and paste the following SQL String into the SQL editing window and save the Query with the name Order_DetailsQ.

SELECT TOP 100 [Order Details].ProductID, [Order Details].UnitPrice
FROM [Order Details]
WHERE ((([Order Details].OrderID) Between 10248 And 10300))
ORDER BY [Order Details].UnitPrice DESC;

Open the Query in Design View and check the order of field placement and the Sort Field.
Right-click on an empty area above the column grid to display the Query Shortcut Menu and select the Properties… option to display the Property Sheet. Check the image below:

Right-click to open Large Image in New Tab/Window.
Check the Top Values Property value set 100, which dictates the Query filter 100 records with the highest Unit Price values.
Change the View of the Query into Datasheet View to display the output records. See the image given below:

Right-click to open Large Image in New Tab/Window.
The Order Details table contains multiple records of the same product under different OrderIDs. In this example, we have intentionally excluded the OrderID field from the data columns, using it only in the criteria to select records with OrderIDs between 10248 and 102300, which also results in some duplicate records. As shown in the image above, several duplicate product records appear in the output. This scenario provides an opportunity to experiment with the Unique Values property settings to remove duplicates.
Eliminating Duplicate Records.
Change the Top Values property to All, and the Query to Datasheet View. The output will be about 150 records for OrderIDs between 10248 and 102300.
Change the Query in Design View and display its Property Sheet.
Set the Top Values property to 100 and the Unique Values property to Yes.
Change the Query in the Datasheet View and inspect the output.
Now the duplicate records are suppressed (29 records removed), leaving only 71 records in the output. The next property, Unique Records, can be set to Yes to achieve the same result when fields from two or more tables or queries are joined in a query design. This is useful when the output contains duplicate records due to a one-to-many relationship between the tables.
We have specified 100 records in the Top Values Property, but the Unique Values property setting reduced the number of records to 71 after suppressing duplicates.
Change the Top Values Property setting from 100 to 25% and change the View to Datasheet.

Using the Percentage setting, the output returns only one-fourth of the total records. With Unique Values set to Yes, this yields 18 records out of a total of 71. With Unique Values set to No, it returns 39 records, which is approximately one-fourth of the total 150 records.

The Top Values Property sets can be a specific number or a percentage of Total Records.