MS Access VBA: turn query results into a single string

Question

I have a table in MS Access composed of items, and there's another table where salespeople tie into those items. So for example the item "pencil" could have 4 people that sold it associated. Once everything comes out of the query I have something like this:

item    seller
pencil  joe
pencil  bob
pencil  charles
pen     john
eraser  ruth
paper   charles

I have a report form where I'd like to list each item's sellers on a single line. Kind of like:

pencil: bob, joe, charles
pen:    john
eraser: ruth
paper: charles

I figure I can pound out a solution where I create an ADO Recordset object from a query statement filtered by item name, and then use a for each cycle to go through the records and feed the names into a concatenate string for each item. Sounds like the long way to do it though. Anyone have a better way of doing it?

Consider ConcatRelated and previous SO posts about it

HansUp
– HansUp

2015-08-20 14:15:53 +00:00
Commented Aug 20, 2015 at 14:15 — HansUp
– HansUp, Commented Aug 20, 2015 at 14:15

HansUp · Accepted Answer · 2015-08-20 15:22:00Z

2

You can create a query using Allen Browne's ConcatRelated function to concatenate the seller names for each item into a separate field. And then use the query in your report's Record Source.

Here is the output from the query below with your sample data stored in a table named YourTable:

item    sellers
------  ------------------
eraser  ruth
paper   charles
pen     john
pencil  joe, bob, charles

SELECT
    y.item,
    ConcatRelated
        (
            "seller",
            "YourTable",
            "[item]='" & [y].[item] & "'"
        ) AS sellers
FROM YourTable AS y
GROUP BY y.item;

edited Aug 20, 2015 at 15:22

answered Aug 20, 2015 at 15:11

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:26:39Z

Jet SQL doesn't natively support any mechanism for concatenating over a group.

However, you could issue a single query with all the data, and use a Scripting.Dictionary (Tools -> References -> Microsoft Scripting Runtime) to hold the results.

Dim dict As New Scripting.Dictionary
Do Until rs.EOF
    If Not dict.Exists(rs!item) Then dict(rs!item) = New Collection
    dict(rs!item).Add rs!seller
    rs.MoveNext
Loop

There is still the problem of generating a string from a collection. I suggest writing a function that takes a collection and returns a string (called Join in the sample below), using one of the two approaches described here.

Once you've done that, you can iterate over the keys in the dictionary, and join the collection in each item:

Dim key As Variant
For Each key In dict.Keys
    Debug.Print key, ":", Join(dict(key), ",")
Next

Collectives™ on Stack Overflow

MS Access VBA: turn query results into a single string

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related