Home > Sample chapters

Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries

Top value queries

Another feature of queries is to limit the number of records displayed, either specifying this as a number of records or as a percentage of the total records that could be displayed.

Figure 6-23 shows a summary query that counts the number of orders for each customer.

FIGURE 6-23

FIGURE 6-23 Customers sorted by the greatest number of orders.

Figure 6-24 shows the query results in the datasheet preview. Note that in the 13 records displayed, the last three records in the screen shot show the same count of 7. This is an important point to note for the following discussion of how the Top feature works in a web app.

FIGURE 6-24

FIGURE 6-24 Resulting datasheet preview showing customers sorted by the greatest number of orders.

Returning to the design view, you can use the Returns icon on the design ribbon to select to display only the top 10 records, as shown in Figure 6-25. (In our example, we typed in the value 10, which is not shown in the drop-down list of choices.)

FIGURE 6-25

FIGURE 6-25 In the design view, a screen that shows the use of the Returns drop-down list to return the top 10 records.

Figure 6-26 shows the resulting top records.

FIGURE 6-26

FIGURE 6-26 The Top Values property returns the top matched records.

If you are familiar with using TOP in an Access desktop database, you might know that it would have returned more than 10 rows in the previous example. This is because if the last row ties with other rows, all matched rows that tie in value are displayed. This is called TOP WITH TIES. Because the web app relies on the Azure SQL Database, which by default does not show tied values, you see only the first of the records that tie for last place.