How to group values from an Ado recordset in memory ? (1 Viewer)

[amorosik]

I have an in-memory Ado recordset that contains four lines of the type
Value Code
100.00 1234
200.00 1234
300.00 3456
400.00 5678

I would like to get a recordset so that the 'Value' field is the sum and grouped by Code, and then something like this
Value Code
300.00 1234 <- sum of 100.00 + 200.00 of the same Code 1234
300.00 3456
400.00 5678

If I had the data on a table, a select like this would suffice
"select sum(Value), Code from TableName group by Code"

But having only one recordset in memory as input parameter, how can I get a second recordset containing the result like the example described above?

 

[ebs17]

Write the contents of the recordset to a text file using the Getstring method. This gives you a text table that can be read and processed by query.

Of course, you can also create a disconnected recordset and write values into it, which you determine and create through loops in the first recordset. But that is more confusing, cumbersome and error-prone and certainly slower.

 

[Edgar_]

In theory, if the recordset is in memory and you can traverse it, then you can use a dictionary and group with its .Exists method.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

rst.MoveFirst
Do While Not rst.EOF
    Dim theCode As String
    theCode = rst.Fields("CodeField").Value

    ' check if it exists
    If dict.Exists(theCode) Then
        ' add if so
        dict(theCode) = dict(theCode) + rst!ValueField.Value
    Else
        ' new code if not
        dict.Add theCode, rst!ValueField.Value
    End If

    rst.MoveNext
Loop

Not tested

 

[amorosik]

In theory, if the recordset is in memory and you can traverse it, then you can use a dictionary and group with its .Exists method.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

rst.MoveFirst
Do While Not rst.EOF
    Dim theCode As String
    theCode = rst.Fields("CodeField").Value

    ' check if it exists
    If dict.Exists(theCode) Then
        ' add if so
        dict(theCode) = dict(theCode) + rst!ValueField.Value
    Else
        ' new code if not
        dict.Add theCode, rst!ValueField.Value
    End If

    rst.MoveNext
Loop

Not tested

Then hold a 'dictionary' like a second recordset?

 

[Edgar_]

Then hold a 'dictionary' like a second recordset?

It won't be a recordset, you'll have an object that no longer works like a SQL database, but that you can get info from, like:
dict("1234") and return 300

So, it depends on what you want to do with the results.

 

[ebs17]

A dictionary is an excellent object in Excel-VBA because you can convert it directly and in one piece into arrays, you can then copy arrays into a worksheet and create a table. However, a table in a database is very different from an Excel spreadsheet.

A recordset is an object. You can't use this like a database table, and you can't "cast" it directly into a database table. Table to recordset is not a problem.

So the first questions should be: Why do you have a recordset as (immutable?) input, what happens to the result after grouping/aggregating? Is the Recordset as an output just an idea, or are there possibly better solutions in terms of subsequent use?
A dictionary is very fast and very suitable for simply looking up values once you have it.

 

[CJ_London]

If you need the data as a recordset you can use a similar method to Edgars solution in post #3 to create a new disconnected ADO recordset and loop through the existing recordset adding or updating as required - edit - see that has been mention in post #2

Probably more efficient to sort the existing recordset into an order first

Or having created a dictionary, loop through that to create a disconnected recordset

 

[amorosik]

If you need the data as a recordset you can use a similar method to Edgars solution in post #3 to create a new disconnected ADO recordset and loop through the existing recordset adding or updating as required - edit - see that has been mention in post #2

Probably more efficient to sort the existing recordset into an order first

Or having created a dictionary, loop through that to create a disconnected recordset

Actually I asked the question to understand was if it was possible to group the records using the classic sql grouping syntax
If it is possible to search and sort, is it not possible to group?

 

[ebs17]

A recordset is NOT editable via SQL.
The options available can be seen in the object catalogue.

 

[CJ_London]

Actually I asked the question to understand was if it was possible to group the records using the classic sql grouping syntax

if by 'classic sql' you mean the temporary queries you can create in some large rdbms's such as sql server, Access does not have that functionality.

only by editing the underlying query (as you would in classic sql) - or base another query (effectively as suggested) on the original one - as you would with classic sql.

but you cannot use a sql statement "select * from anyrecordset"

what you could do is something like

newadors.source="SELECT Code, sum(value) FROM (" & originalRS.Source & ") GROUP BY Code"

Not relevant to your question but if you are new to ADO be aware that if you are using ado recordsets to display on a form, the right click functions to sort and filter will error since they are based on DAO methods. This can be overcome by writing your own right click menus using ADO methods for sorting and filtering.

 

[AHeyne]

@amorosik : Filtering in an ADO recordset is also quite limited.

Some time ago I played a bit with filtering ado recordsets and found out:
- You cannot use VBA methods like `Len`, `Trim` or `NZ` in the filter string.
- For the empty string, single quotes must be used. Quotation marks do not work.
- A filter on the empty string will also have a field containing only spaces in the result.
- `And` and `Or` cannot be combined arbitrarily:
- There is no rank order between `And` and `Or`. Clauses can be grouped within parentheses.
- However, it is not possible to group clauses connected by an `Or` and then connect the group to another clause by an `And`, as described below.
- `Not` cannot be used at all.

 

[MarkK]

You can achieve the result in post #1 within a single disconnected recordset with code like...

    Dim tmpValue As Single
    Dim lastCode As Integer
    
    With rs
        .MoveFirst
        Do While Not .EOF
            If lastCode <> !Code Then lastCode = !Code
            .MoveNext
            If Not .EOF Then
                If lastCode = !Code Then        ' is duplicate Code row
                    .MovePrevious               ' go back
                    tmpValue = !Value           ' get previous Value
                    .Delete                     ' delete previous row
                    .MoveNext                   ' get next row
                    !Value = tmpValue + !Value  ' sum previous row value
                    .Update
                End If
            End If
        Loop
    End With

As long as the Code field doesn't change, we keep accumulating the summed Value in the current row, and deleting previous duplicates.

 

[Pat Hartman]

If it is possible to search and sort, is it not possible to group?

searching and sorting does not change the underlying records. Grouping does. The grouping would be permanent (unlike the sorting) unless two separate recordset objects are used.