Microsoft KB Archive/232593

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

Article ID: 232593

Article Last Modified on 7/31/2001

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q232593



Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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
To create the query or the view, follow these steps:  Open the sample database Northwind.mdb or the sample Microsoft Access project NorthwindCS.adp.  Create the following query or view based on the "Sales by Category" query or view.

Query in Northwind.mdb (an Access database)
 In the Database window, click Queries under Objects, and then click New. Click Design View and click OK.  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  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%.</li> Close and save the query as TopTen.</li></ol>

View in NorthwindCS.adp (an Access project)
<ol style="list-style-type: lower-alpha;"> In the Database window, click Views under Objects, and then click New.</li> On the View menu, point to Show Panes, and then click SQL.</li>  Type the following SQL statement: <pre class="fixed_text">  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. </li> 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.</li></ol> </li></ol>

Creating the Top 10 Page
To create the Top 10 page, follow these steps: <ol> In the Database window, click Pages under Objects, and then click New.</li> 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)</li>  On the View menu, click Sorting and Grouping. Set the following properties: <pre class="fixed_text">  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.

</li>  Add the following controls to the Top 10 page and set the properties of the controls as follows: <pre class="fixed_text">  Text Box ---  Id: txtRank Text Box --  Id: ProductName ControlSource: ProductName Text Box ---  Id: CategoryName ControlSource: CategoryName

Text Box Id: Sales ControlSource: Sales </li> Move the labels of the text boxes to the caption section of the page.</li> 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.</li> On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> Click the HTML Outline tab. Double-click txtRank.</li>  On the Source tab, add a name to the TEXTAREA tag for the text box with the Id of txtRank. For example, change: <pre class="fixed_text">  "... <TEXTAREA id=txtRank style="HEIGHT: 0.166in; ..."  to <pre class="fixed_text">   "... <TEXTAREA id=txtRank name=txtRank style="HEIGHT: 0.166in; ..." </li>  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)> </SCRIPT> </li> <li>On the File menu, click Save. In the File name box, type dapTopTen, and then click Save.</li> <li>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.</li></ol>

Keywords: kbhowto kbdap kbdapscript KB232593

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.