ACC: Adding Dynamic Counter to Query to Count Records

Article translations Article translations
Article ID: 94397 - View products that this article applies to.
This article was previously published under Q94397
Advanced: Requires expert coding, interoperability, and multiuser skills.

Expand all | Collapse all

On This Page

SUMMARY

This article describes how to use a dynamic counter value in a query field for the following purposes:
  • Returning the top x number of records to ship in a Microsoft Access version 1.x query.

    NOTE: In Microsoft Access versions 2.0 you can use the TopValues query property to accomplish this.
  • Displaying the record's position number on a form. For more information about this topic, query on the following words here in the Microsoft Knowledge base:
    record and position and number and form
  • Printing the top x number of records on a report. For more information about this topic, query on the following words here in the Microsoft Knowledge Base:
    top and 10 and records and report
    NOTE: If the report includes totals, totals will be displayed for all the records, not just the top x number of records. To total only the top x number of records, limit the records included in the report using the method described later in this article.
This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.

MORE INFORMATION

Sample code to implement a dynamic counter is described later in this article. There are several limitations to this method, including the following:
  • You must reset the global counter to 0 before you run the query each time. You can use the function SetToZero() to do this.
  • Any scrolling, minimizing, maximizing, switching focus away and back, and so on, causes a repaint to occur, which in turn causes the query to call the function again. For this reason, you should not use this function in a select query. Instead, you should base a report on the select query or run a make-table query and view the resulting table.
  • When basing a report on a select query with this function, scrolling back through the pages will cause the function to be called multiple times, and the counter expression will be off by the total number of records in the recordset.
  • When you place criteria on the counter expression, the function will be called twice per record. For example, if you specify criteria of <=10 to return the first 10 records, the recordset will contain 10 records, but the counter expression will be off by the total number of records in the recordset.
  • Do not place the counter expression in the first column of the query; the number will be off by one.
  • Do not combine criteria and sorting in a query with the counter expression. The criteria will be applied before the sort. Instead, you must create a make-table query with the sort, and then base a second query containing the counter expression on the new table with sorted data.

The Dynamic Counter Function

The following Access Basic functions, Qcntr() and SetToZero(), can be used to implement a dynamic counter in a query. To use the Access Basic functions do the following:
  1. Create a new module.
  2. Type the following declarations in the Global Declarations section (omit the Option Explicit line if it already exists):
    'Global Declarations Section.
    Option Explicit
    Global Cntr
    					
  3. Type the following functions in the module:
    '*************************************************************
    ' Function:  Qcntr()
    '
    ' Purpose: This function will increment and return a dynamic
    ' counter. This function should be called from a query.
    '*************************************************************
    
    Function QCntr(x) As Long
       Cntr = Cntr + 1
       QCntr = Cntr
    End Function
    
    '**************************************************************
    ' Function:  SetToZero()
    '
    ' Purpose: This function will reset the global Cntr to 0. This
    ' function should be called each time before running a query
    ' containing the Qcntr() function.
    '**************************************************************
    
    Function SetToZero()
       Cntr = 0
    End Function
    					

Returning the Top X Records

To return the 10 most expensive orders to ship, follow these steps:
  1. Open the sample database NWIND.MDB.
  2. Create a new query based on the Orders table. Include the following fields:
       Field: Order ID
       Field: Customer ID
       Field: Order Date
       Field: Freight
          Sort: Descending
       Field: Expr1: Qcntr([order id])
    					
  3. From the Query menu, choose Make Table. When you are prompted for a table name, type Sorted Orders.
  4. Save the query as Make Sorted Orders.
  5. Create a new query that will be based on the Sorted Orders table after it is created. For now, close the Add Table dialog box without adding a table to the query. From the view menu, choose SQL, and then enter the following SQL statement:
    Select * from [Sorted Orders] where [Expr1] <= 10
  6. Save this query as Top 10 Orders, and then close it.
  7. Create the following macro:
       Actions
       -----------
       RunCode
       SetWarnings
       OpenQuery
       OpenQuery
    
       Macro Actions
       -----------------------------------
       RunCode
    
          Function Name: SetToZero()
       SetWarnings
          Warnings On: No
       OpenQuery
          Query Name: [Make Sorted Orders]
       OpenQuery
          Query Name: [Top 10 Orders]
    					
  8. Run the macro. The table Sorted Orders will be created, and then the Top 10 Orders query will be displayed.
NOTE: Because the Top 10 Orders query is based on a table, you can scroll through the records and use criteria on the counter expression without causing the function to be called multiple times per record.

Creating a Top 10 Report That Includes Totals

To create a Top 10 Report that includes totals, follow these steps:
  1. Repeat steps 1-5 in the above section.
  2. Create a new report called Top 10 Orders based on the Top 10 Orders query.
  3. Create the following new macro:
       Actions
       -----------
       RunCode
       SetWarnings
       OpenQuery
       OpenReport
    
       Macro Actions
       -----------------------------------
       RunCode
    
          Function Name: SetToZero()
       SetWarnings
          Warnings On: No
       OpenQuery
          Query Name: [Make Sorted Orders]
       OpenReport
          Report Name: [Top 10 Orders]
    					
  4. Run the macro.
NOTE: The counter expression will not renumber if you change the sort order or add grouping in the report because it is calculated in the make-table query. The counter expression is being used here to limit the recordset to the top 10 orders.

REFERENCES

For information about when functions are executed in queries, please see the following article in the Microsoft Knowledge Base:
98788 ACC: Number of Times a Custom Function Executes in a Query

Properties

Article ID: 94397 - Last Review: May 9, 2003 - Revision: 2.0
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
Keywords: 
kbhowto kbusage KB94397
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com