Sum, Avg, Stdev Next X Records While In Loop Of Recordset RRS feed

  • Question

  • I know this is a very specific question and I appreciate the extra time it will take to read and understand precisely what I'm asking.

    I'm looking for a function or techniques to count, sum, average, min, max or standard deviation while already evaluating the recordset with a Do Until eof() .... MoveNext.. Loop.

    For example, after a recordset has been Set and a Do Until rs.eof() .... rs.MoveNext Loop created. Let's assume you've come to a point in stepping through the rs where you want to do some summary statistics on a group of records (assuming the records are Indexed).

    Assume recordset is using an index on [Product Code]. You're moving through the recordset and product code's. We've come to product code X in the recordset.

    I could start a counter for [Product Code] = "X" and count the number of records while [Product Code] = "X".
    I could start a summation of the sales column values to a memory variable while [Product Code] = "X".
    When product code shifts from "X" to "Y", I could then calculate the Average Sales by dividing the sales summation variable by the counter variable.

    My question is, are there functions that will count, sum, average, etc while steping through a recordset?

    Something like:

    dblSales = Sum rs![Sales] while rs![Product Code] = "X"

    intCounter = Count while rs![Product Code] = "X"

    dblAvgSales = Average rs![Sales] while rs![Product Code] = "X"

    dblStDevSales = StDev rs![Sales] while rs![Product Code] = "X"

    I realize I would have to move the recorder pointer back to the start of Product Code "X" for each of the above functions, but if the functions exist, it would be easier than manually calcualating with memory variables.

    I understand and use domain aggregate functions and realize queries could be ran do accomplish this information.  But this is a specific question about functions available for use within active recordsets.   The answer maybe no, there are no summary functions for use in the situation I'm describing.

    I appreciate the extra time in understanding this question. Best Regards.... Ron.

    • Moved by Mike Feng Wednesday, July 20, 2011 9:50 AM VB6 (From:Visual Basic General)
    Monday, July 18, 2011 4:37 PM

All replies