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?
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;
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).
One of the properties is Top Values. In Figure 1-17, you can see that the value of 40 is already entered.
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;
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.
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
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.