MS Access VBA: turn query results into a single st

问题:

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?

回答1:

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;

回答2:

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