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

• Microsoft Access 2000

SUMMARY

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.

MORE INFORMATION

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

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 then click OK.



3. Create the following query based on the "Sales by Category" query:




   Query: qryTop10
   ---------------------------
   Field: ProductName
   Total: Group By

   Field: CategoryName
   Total: Group By

   Field: Sales: ProductSales
   Total: Sum
   Sort:  Descending 

4. Right-click an empty area in the query design grid, and then 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

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 indicated:




   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 oEventInfo parameter added above is used to return specific information about the event to the script. You must add this parameter, regardless of whether it will be used or not, because the script won't 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.



13. On the View menu, click Page View. Note that the page displays the 10 products with the highest sales amount.

Additional query words:

Keywords : kbdta AccDAP DAPScriptHowTo dtavbscript
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto