Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries
- 2/9/2015
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 Customers sorted by the greatest number of orders.
TIP
In a web app, you cannot create a copy of an existing query. This means if you need several similar queries, you must create each one from a new query. (You can use the clipboard to copy and paste complex query expressions from one query to the next.)
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.
){kind=link}
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.)
){kind=link}
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.
TIP
If you look at the query properties, you will see that although the ribbon displays the word Returns, the property is called Top Values.
Figure 6-26 shows the resulting top records.
){kind=link}
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.
TIP
The Azure SQL Database does offer a special TOP WITH TIES feature, but this is not available for you to use in a web app.