problem with concatenating fields in the same column from multiple rows

[banderson]

Hello all,
I have a table "tblContacts" that holds my Contact Info. Because there can be multiple Contact Types for a Contact, I have a second table "tlnkContactTypes" in which there is a record for every Contact / Type combination.

tblContacts: ContactID, NmFirst, NmLast. etc...

tlnkContactTypes: ContactID, ContactType (which is selected from value list)

For a summary query I need to Show all of the Contact Types for a contact together. I have found a public function called "Conc" (see below) that does a nice job of concatenating all Contact Types for a ContactID from tlnkContactTypes into one field.

So tlnkContactTypes
ContactID ContactType
1 Resident
1 Super
2 Resident
2 Teacher
3 Teacher

is transformed to qryContactTypesCnct

ContactID ContactTypes
1 Resident, Super
2 Resident, Teacher
3 Teacher

The problem that I have is that not all Contacts are represented in tlnkContactTypes, because a Contact may not have a Type. When I create my summary query based on tblContacts and include qryContactTypesCnct, I get an error message for every record in tblContacts that is not represented in qryContactTypesCnct.

I cannot figure out how to make it enter a "blank" value for Contacts that are not in qryContactTypesCnct. Any advice would be appreciated!

Banderson

For my project, the variables in the function below are define as follows:
Fieldx = ContactType",
Identity = "ContactID",
Value = [ContactID],
Source = "tlnkContactTypes"

I use the public function below in my qryContactTypesCnct SQL like this:

SELECT tlnkContactTypes.ContactID, Conc("ContactType","ContactID",[ContactID],"tlnkContactTypes") AS ContactTypes
FROM tlnkContactTypes
GROUP BY tlnkContactTypes.ContactID;

 
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim Rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant
 
  Set cnn = CurrentProject.Connection
  Set Rs = New ADODB.Recordset
  vFld = Null
 
  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity & "]=" & Value
 
  ' open recordset.
  Rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
 
 
  ' concatenate the field.
  Do While Not Rs.EOF
    If Not IsNull(Rs!Fld) Then
      vFld = vFld & ", " & Rs!Fld
    End If
    Rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)
 
  Set cnn = Nothing
  Set Rs = Nothing
 
  ' return concatenated string.
  Conc = vFld
End Function
 
 

[ADezii]

I took a different approach, simply because I am at work and needed the quickest possible solution, for me.

1. Sample Data from tblContacts:
   

   ContactID    ContactType
   1            Resident
   1            Super
   2            Resident
   2            Teacher
   3            Teacher
   4    
   5    
   6            Resident
   6            Teacher
   6            Super
   6            Client
   6            Doctor
   6            Lawyer
   6            Indian Chief

2. SQL Statement for Query:
   

   SELECT tblContacts.ContactID, Conc([ContactID]) AS Types, fTotalOfTypes([ContactID]) AS [Total Types]
   FROM tblContacts
   GROUP BY tblContacts.ContactID
   ORDER BY tblContacts.ContactID;

3. Function Definitions:
   

   Public Function Conc(lngContactID As Long) As String
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strBuild As String
    
   strSQL = "SELECT * FROM tblContacts WHERE [ContactID] = " & lngContactID
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
   With rst
     Do While Not .EOF
       strBuild = strBuild & ![ContactType] & ","
       .MoveNext
     Loop
   End With
    
   Conc = Left$(strBuild, Len(strBuild) - 1)
    
   rst.Close
   Set rst = Nothing
   End Function

   Public Function fTotalOfTypes(lngContactID As Long) As Long
     fTotalOfTypes = DCount("*", "tblContacts", "[ContactID] = " & lngContactID & _
                           " And [ContactType] Is Not Null")
   End Function

4. Output after Query Execution:
   

   ContactID    Types                                                        Total Types
   1            Resident,Super                                                     2
   2            Resident,Teacher                                                   2
   3            Teacher                                                            1
   4                                                                               0
   5                                                                               0
   6            Resident,Teacher,Super,Client,Doctor,Lawyer,Indian Chief           7

[banderson]

@ADezii
ADezii,
This works beautifully! and solves my problem
Thank you so much for the help.

Banderson

[ADezii]

You are quite welcome.