O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Returning a Top or Bottom Number of Records

Problem

I have a large table of data that contains thousands of records and several dozen fields. I create models based on various fields and/or ranges of values in the fields. I use queries to set up the sums using SQL aggregates and expressions. This is exactly what I need, but the problem is that the number of records slows down the processing. When I'm testing calculations, I don't need all the records. How can I pull out just a handful of them to use for testing?

Solution

The SQL TOP predicate is just what is called for here. It lets you specify how many records to return, either as an exact number or as a percentage of the total number of records in the underlying table or query.

Let's say you have a standard select query such as the one shown in Figure 1-16. The SQL statement is:

	SELECT SampleNum, Identifier, Fact1, Fact2,
	Fact3, Fact4, Fact5, Fact6, Fact7, Fact8
	FROM ConsumerTrendData;
A simple select query returns all records

Figure 1-16. A simple select query returns all records

To specify a subset of records to search through to test the query—say, 40—use the TOP predicate, as follows:

	SELECT TOP 40 SampleNum, Identifier, Fact1,
	Fact2, Fact3, Fact4, Fact5, Fact6,
	Fact7, Fact8
	FROM ConsumerTrendData;

TOP comes directly after the SELECT keyword, and is followed by the number of records to return. Instead of reducing the number of returned records based on criteria, TOP reduces the number of returned records without any bias.

When working with the Access query grid, you can opt to use TOP by going into the query properties. To do this, use the View → Properties menu option while designing the query. The properties sheet that opens may display the properties for a field. If this is the case, click on the top pane of the query designer (above the grid)but not on any tables—in other words, click on the empty area. This will ensure that the properties sheet displays the query properties (see Figure 1-17).

The Query Properties sheet

Figure 1-17. The Query Properties sheet

One of the properties is Top Values. In Figure 1-17, you can see that the value of 40 is already entered.

Discussion

To return a percentage of the records, you can place a percent sign (%) after the entered number in the Top Values property on the properties sheet, or you can enter the word PERCENT directly in the SQL statement. Here, for example, is the SQL to return the top 20 percent of the records:

	SELECT TOP 20 PERCENT SampleNum, Identifier,
	Fact1, Fact2, Fact3, Fact4, Fact5,
	Fact6, Fact7, Fact8
	FROM ConsumerTrendData;

Using TOP to return the "top" X number of records begs the question of what makes the hierarchy of records in a table. Only the application of an index or sort provides any structure to the records. We often use AutoNumber fields, which order the records. But what happens when we sort on another field? The "top" records change.

Using the TOP predicate requires that the use of a sort, or lack thereof, always be considered. Here is an example of returning the top five records of a sorted table:

	SELECT TOP 5 SampleNum, Identifier, Fact1,
	Fact2, Fact3, Fact4, Fact5, Fact6,
	Fact7, Fact8
	FROM ConsumerTrendDataORDER BY Identifier;

Now that we've sorted the data in ascending order (the default sort direction)with the ORDER BY clause, asking for the top five records has some relevance. Turning this upside down would provide the bottom five records. But how could we do that? There is no "bottom" predicate. Instead, we simply change the sort to descending using the DESC keyword:

	SELECT TOP 5 SampleNum, Identifier, Fact1,
	Fact2, Fact3, Fact4, Fact5, Fact6,
	Fact7, Fact8
	FROM ConsumerTrendData
	ORDER BY Identifier DESC;

This example requests a descending sort on the Identifier field. Requesting the top five records will now return what were the bottom five records when we did an ascending sort. Figure 1-18 shows the results of running these two queries. The sort on the Identifier field is ascending in one query and descending in the other.

Ascending and descending sorts

Figure 1-18. Ascending and descending sorts

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required