And the next one; CONTAINS in query

[Egg 'n' Bacon]

This is really doing my head in; I have a field in a sub-table that can contain any combination of the following; CN, AP, CL, CW, HW, etc (anything from none of these to all of them).

I need a summary query that will display (grouped by parent-table ID) which of these is present within the related records, but on one line.

For example, if there were 3 records for (parent-table) ID AA01 with the following;
1. CN, AP
2. AP, CW, HW
3. CL

I would need the query to denote which of the designated codes are present. So the result would have; CN, AP, CW, HW, CL on one line (or some other method to demonstrate presence)

[HansV]

This one is not trivial.

Copy the following code into a module:

Code: Select all

' Concatenate unique field values in a table or query
' with optional where-condition.
' The function uses a DAO recordset, so parameter queries and
' Access-specific features will cause an error.
' Example usage: create a text box with control source
'   =DConcatUnique("Surname","Persons")
' or
'   =DConcatUnique("Surname","Persons","Gender='M'","/")

Function DConcatUnique _
  (FieldName As String, _
   TableName As String, _
   Optional WhereCondition As String, _
   Optional Delimiter As String) As String
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim strRes As String
  Dim col As New Collection
  Dim strParts() As String
  Dim i As Long

  On Error GoTo ErrHandler

  If WhereCondition <> "" Then
    strSQL = " AND (" & WhereCondition & ")"
  End If
  If Delimiter = "" Then
    Delimiter = ", "
  End If
  If strSQL <> "" Then
    strSQL = " WHERE" & Mid$(strSQL, 5)
  End If
  strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]" & _
    strSQL
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)
  Do While Not rst.EOF
    strParts = Split(rst(FieldName), Delimiter)
    On Error Resume Next
    For i = LBound(strParts) To UBound(strParts)
      col.Add strParts(i), CStr(strParts(i))
    Next i
    On Error GoTo ErrHandler
    rst.MoveNext
  Loop
  Call BubbleSort(col)
  For i = 1 To col.Count
    strRes = strRes & Delimiter & col(i)
  Next i
  If strRes <> "" Then
    strRes = Mid$(strRes, Len(Delimiter) + 1)
  End If
  DConcatUnique = strRes

ExitHandler:
  On Error Resume Next
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Function

ErrHandler:
  DConcatUnique = "--Error--"
  Resume ExitHandler
End Function

Sub BubbleSort(ByRef col As Collection)
  Dim varTemp As Variant
  Dim i As Long
  Dim j As Long
  For i = 1 To col.Count - 1
    For j = i + 1 To col.Count
      If col(i) > col(j) Then
        varTemp = col(j)
        col.Remove j
        col.Add varTemp, CStr(varTemp), i
      End If
    Next j
  Next i
End Sub

Create a query based on your subtable.
Change it to a Totals query.
Add the parent ID field to the query grid.
Next to it, create a calculated column

List: DConcatUnique("TextField", "SubTable", "IDField=" & Chr(34) & [IDField] & Chr(34))

where SubTable = name of the table, IDField = name of the parent ID field, and TextField is the name of the field with the combinations of strings.

I have attached a sample database.

ConcatTest.zip

[Egg 'n' Bacon]

Not sure why, but I'm getting --Error--

I've attached a stripped down version. Hopefully you'll be able to see where I've gone wrong (fingers crossed)

ConcatTester.zip

[HansV]

You have defined the calculated column as

Impakt: DConcatUnique("[HazardImpact","tblSub","ID=" & Chr(34) & [ID] & Chr(34))

You should remove the stray opening bracket [ before the field name:

Impakt: DConcatUnique("HazardImpact","tblSub","ID=" & Chr(34) & [ID] & Chr(34))

[Egg 'n' Bacon]

Doh!

It is so often the little things that trip me up.

Thank you Hans