ACC2000: How to Create a Top 10 List on a Data Access Page

This article describes how to create a Top 10 list on a data access page, that is, a page that lists the first 10 records based on the sort order. The example in this article shows how to create the page for both a Microsoft Access database and a Microsoft Access project.

In the following example, you first create a query or a view based on the "Sales by Category" query, and then the Top 10 data access page.

Creating the Query or the View

To create the query or the view, follow these steps:

1. Open the sample database Northwind.mdb or the sample Microsoft Access project NorthwindCS.adp.
2. Create the following query or view based on the "Sales by Category" query or view.

   Query in Northwind.mdb (an Access database)

   1. In the Database window, click Queries under Objects, and then click New.
   2. Click Design View and click OK.
   3. Create the following query based on the "Sales by Category" query:

         Query: TopTen
         ---------------------------
         Field: ProductName
         Total: Group By
      
         Field: CategoryName
         Total: Group By
      
         Field: Sales: ProductSales
         Total: Sum
         Sort:  Descending

   4. Right-click within an empty area of the query design grid and click Properties on the shortcut menu. Set the TopValues property to 10.
      
      NOTE: You can use any number or percentage that you want. For example, you can set the TopValues property to 15%.
   5. Close and save the query as TopTen.

   View in NorthwindCS.adp (an Access project)

   1. In the Database window, click Views under Objects, and then click New.
   2. On the View menu, point to Show Panes, and then click SQL.
   3. Type the following SQL statement:

         SELECT TOP 10 ProductName, CategoryName,  
                SUM(ProductSales) AS Sales
         FROM [Sales By Category]
         GROUP BY CategoryName, ProductName
         ORDER BY Sales DESC

      NOTE: You can use any number or percentage that you want. For example, you can type 15 PERCENT in the SQL statement.
   4. Close and save the view as TopTen.
      
      NOTE: When you save the TopTen view, you receive the following error message:
      Order by clause may not be used in this query type.
      However, the view returns the results in the correct order. Click OK to close the dialog box.

Creating the Top 10 Page

To create the Top 10 page, follow these steps:

1. In the Database window, click Pages under Objects, and then click New.
2. Create a new data access page, and set the RecordSource property to either Query: TopTen (in an .mdb) or Recordset: dbo_TopTen (in an .adp)
3. On the View menu, click Sorting and Grouping. Set the following properties:

      Caption Section: Yes
      Record Navigation Section: No
      Data Page Size: 10

   NOTE: Set the DataPageSize property to the number of records that you expect to get, based on the value that you specify in step 2 of the "Creating the Query or the View" section.
   
   
4. Add the following controls to the Top 10 page and set the properties of the controls as follows:

      Text Box 
      -----------
      Id: txtRank
        
      Text Box 
      --------------------------
      Id: ProductName
      ControlSource: ProductName
        
      Text Box 
      ---------------------------
      Id: CategoryName
      ControlSource: CategoryName
   
      Text Box 
      --------------------
      Id: Sales
      ControlSource: Sales

5. Move the labels of the text boxes to the caption section of the page.
6. Move the text boxes to the top of the header section of the page under the corresponding labels, and decrease the size of the header section.
7. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
8. Click the HTML Outline tab. Double-click txtRank.
9. On the Source tab, add a name to the TEXTAREA tag for the text box with the Id of txtRank. For example, change:

      "... <TEXTAREA id=txtRank style="HEIGHT: 0.166in; ..."

   to

      "... <TEXTAREA id=txtRank name=txtRank style="HEIGHT: 0.166in; ..."

10. Using the Script Outline, insert the following script for the Current event of the MSODSC.IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name as follows:
    <SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>
    The <I>oEventInfo</I> parameter returns specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script will not work without it.
    

    <SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>
     <!--
      Dim cnt
      cnt = 1
    
      For Each RnkBox In document.all.item("txtRank")
          RnkBox.value = cnt
          cnt = cnt + 1
      Next
     -->
    </SCRIPT>

11. On the File menu, click Save. In the File name box, type dapTopTen, and then click Save.
12. On the File menu, click Exit, then on the View menu, click Page View.
    
    Note that the page displays the 10 products with the highest sales amount.