New Sample: Query_RowNumbersAndSumLargeData

Query_RowNumbersAndSumLargeData
Author: A.D. Tejpal

Subqueries (or user defined functions based upon recordsets) for calculation and display of row numbers and running sum, are found to be slow in case of large data sets.

Use of increments to global variables offers a faster alternative. This method is preferably used through an action query, as calculated values directly displayed via select query tend to be volatile (the results keep on changing as one navigates up and down the records.

Two alternative methods based upon incrementing global variables are demonstrated as follows. In each case, two styles are covered, namely (a) Straight simple sequence and (b) Group-wise sequence. If it is simple sequence, the user defined functions have provision for specifying the starting number or sum - if desired:
1 - Row number and running sum - via update action.
2 - Row number and running sum - via make table action.

For academic interest, depiction of row numbers through generation of autonumbers has also been demonstrated as follows (this approach is however not as fast as that involving increments to global variables):
3 - Row number - via append action..
4 - Row number - via make table action.

Note:
(a) Use of incrementing global variables (items 1 and 2 above), apart from being the fastest approach, has the added benefit that it is not dependent upon availability of primary key.
(b) Four user defined functions utilized in implementing this method are given in the general module.
(c) Use of these functions in WHERE clause of pertinent queries is meant to initiate the sequence in desired manner.
(d) If filter has been applied to displayed data, generation of row number and running sum gets implemented on the filtered data, as demonstrated in the sample db.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=433&PID=429#429