Collapse All Expand All

To display only the top ten (or other number) of details on a report, you can manipulate the data pulled by your SQL query.

To set an Access data source to pull top ten data

1. Click the gray report DataSource icon on the Detail section band to open the Report Data Source dialog.
   
2. On the OLE DB tab, next to Connection String, click the Build button.
3. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
4. Click the ellipsis (...) button to browse to the Northwind database. Click Open once you have selected the appropriate access path.
5. Click OK to close the window and fill in the Connection String field.
6. In the Query field, paste the following SQL query.

   SQL Query	Copy Code
   SELECT TOP 10 Customers.CompanyName, Sum([UnitPrice]*[Quantity]) AS Sales FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Customers.CompanyName ORDER BY Sum([UnitPrice]*[Quantity]) DESC

7. Click OK to return to the report design surface.

To add controls to display the top ten data

1. In the Report Explorer, expand the Fields node, then the Bound node.
2. Drag the following fields onto the detail section and set the properties of each textbox as indicated.

   Field	Text	Location	Miscellaneous
   CompanyName	Company Name	0.5, 0
   Sales	Sales	5, 0	OutputFormat = Currency