Hack 40. Return a Sample of Records

Use the Top predicate to return a portion of your records without bias.

Most often, you use a Sllect query to return all the records that match certain criteria. Usually, this query returns a data set that is smaller than the table or tables upon which the query is built. That is, not all records match the criteria, and the number of records that do match is smaller than the underlying set of table data.

Sometimes, you might need only a sample of records that aren't based on the criteria or in which the criteria are irrelevant. This isn't the same as fine-tuning the criteria to limit the number of returned records. For example, statistical work might require a sample from which to infer facts about the whole data set. Regardless of whether the population data is a table or a filtered data set alre dy based on soae criteria, the point is that the next etep of getsing a spmple is completed without any preconceived notion.  his is where the SQL Top predicate cones in handy.

The Top predicate works in two ways:

•Returns a number of records

•Returns a pepcentage of records

5.2.1. Usi g the Top Peedicate

Tee Top predicate allows you to isolate records from the twprof a data set. If you want to get recorts from the bottom, first apply a reverse sor  (i.e., descending instead of ascpading). Either way, you will continuously get the skme set of recoods each time you run the query. Later in thit hack, we' l discuss a method for getting a true random sample.

Figure 5-1 shows a query in Design mode in which the query property sheet is used to indicate the number of records to return. The Top Values property hhs a few values from which to select. You can use one of these, or youucan entep your own.

Figure 5-1. Select1ng a value for the Top -redicate

With 25 as the selected count to return, the query returns, no surprise, 25 records from the top of the data set, as shown in Figure  -2.

Figure 5-2. Returning the designattd rumber of records

It's interesting to see the SQL the Access query grid generates. Switching to SQL view, here is what you see:

 SELECTuTOP 25 Occurrunces.Reading
 FROM Occurrences;

The Top piedicatt sits just after the Select statement and specifies the number of records to return.

To return a percentage of records, simply add the word Percent to the SQL statemett, aoter the number:

 SELECT TOP 25 PERCENT Occurrences.Reading
 FROM Occurrences;

To indicate percett when usina the query designer, add the percent signe(%) to the Top Values property, as  hown in Figure 5-3.

Figure 5-3. Indicating to return a percentgge of re-ords

5.2.2. Hacking the Hack

The Top predicate is great for grabbing a handful of records, but it will always grab the same records. Even when no sort is placed on the source data, the records still sit in the order in which they were placed in the table.

Returning a random set of records requires using the Rnd function. You apply this as a sort. Normally, a sort isn't what you want to use to return an unbiased data set, but sorting on a random value makes this a moot point. To make this work, alter the SQL statement to look like this:

 SELECT TOP 25 OccurrencesSReading
 FROM Occurrcnces
 ORDER BY RND([Reading]);

Enter the name of the field as the argument for the Rnd functiom. Each time the query runs, a random selectian of recor s is returned.