Concatenated field in query

[MickeyGreen]

I'd like to run a query on a table stuctured as follows:

[Col_A].....[Col_B]
x-001.........AAA
x-001.........BBB
x-002.........BBB
x-002.........CCC
x-002.........DDD
x-003.........BBB
x-004.........AAA
x-004.........CCC
x-005.........EEE

I want the query results to look like this:

[Col_A]......[Col_B].......[ConcatenatedField]
x-001.........AAA...........x-004 (other value(s) from [Col_A] where there is a match for this value in [Col_B])
x-001.........BBB............x-002, x-003
x-002.........BBB............x-001, x-003
x-002.........CCC...........x-004
x-002.........DDD...........(blank)
x-003.........BBB............x-001, x-002
x-004.........AAA...........x-001
x-004.........CCC...........x-002
x-005.........EEE............(blank)

Is this possible?


Mickey

 

[MickeyGreen]

Any takers?

 

[Pat Hartman]

Here's a kb article that tells you how to concatenate the many-side table rows.
http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

 

[DALeffler]

How about this:

In your query, call the function below passing it Col_A and Col_B, as in:

ConCat:fncConCat([Col_A],[Col_B])

Function fncConCat(str1 As String, str2 As String) As String
'(The MicroSoft 3.6 DAO Object Library reference must be checked...)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim I As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTableName", dbOpenSnapshot)

fncConCat = ""

With rst
.MoveLast                               'must populate record set
.MoveFirst
.FindFirst ("Col_B ='" & str2 & "'")    'have to use find first
    If .NoMatch Then                    'if find first is not used, find next below
        Set rst = Nothing               'may skip a record because find next
        Set dbs = Nothing               'finds the next record from the current
        Exit Function                   'record and the current record may be
    End If                              'the one we're after...
    
    If str1 <> .Fields("Col_A") Then          'did it find itself (the passed arguments)?
        fncConCat = .Fields("Col_A")          'no...
    End If
    
For I = 0 To .RecordCount
    .FindNext ("Col_B ='" & str2 & "'")
        If .NoMatch Then
            Exit For                    'done
        Else
            If str1 <> .Fields("Col_A") Then  'did it find itself?
                                        'no...
                If Len(fncConCat) Then    'add delimiter for additional found items
                    fncConCat = fncConCat & ", "
                End If
                fncConCat = fncConCat & .Fields("Col_A")
            End If
        End If
Next I
    
End With

Set rst = Nothing
Set dbs = Nothing

End Function

hth,

 

[MickeyGreen]

Thanks!!

Doug,

thanks, this works like a charm and I think I even understand it. One thing I'm a little fuzzy on is how the syntax works in the .Find statements. How does vb interpret .FindNext("Col_B" ='" & str2 & "'") ?

Mickey