problem with concatenating fields in the same column from multiple rows

[banderson]

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

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

tlnkContactType s: 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 tlnkContactType s into one field.

So tlnkContactType s
ContactID ContactType
1 Resident
1 Super
2 Resident
2 Teacher
3 Teacher

is transformed to qryContactTypes Cnct

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

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

I cannot figure out how to make it enter a "blank" value for Contacts that are not in qryContactTypes Cnct. 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 = "tlnkContactTyp es"

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

SELECT tlnkContactType s.ContactID, Conc("ContactTy pe","ContactID" ,[ContactID],"tlnkContactTy pes") AS ContactTypes
FROM tlnkContactType s
GROUP BY tlnkContactType s.ContactID;

[code=vb]

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connectio n
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, adOpenForwardOn ly, 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

[/code]

[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]

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

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

Banderson

[ADezii]

You are quite welcome.