Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_RowNumbersAndSumLargeData
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Query_RowNumbersAndSumLargeData

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_RowNumbersAndSumLargeData
    Posted: 09 Oct 2008 at 4:44pm

Query_RowNumbersAndSumLargeData
Sample Db - Explanatory Notes

    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.

    Reference required - DAO 3.6

    Version - Access 2000 file format

 
--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.