VBA Concatenation Assistance Request

[USTRAGNU1]

Team Bytes,

I have a query that is outputting a few duplicate records because of two fields, [HR Reporting].[Type of Occupational Cert] and [HR reporting].[Date Occupational Cert Issued]. I would like to output one record with the multiple certifications and dates concatenated in one field. Example of current output:

Name Certs Cert Date
Doe Jane CISSP 1/2/03 (the rest of the fields are the same)
Doe Jane SEC+ 2/3/04
Doe Jarl C++ 3/4/05
Doe John Net+ 4/5/06


Would like to display:

Name Certs And Dates
Doe Jane CISSP 1/2/03; SEC+ 2/3/04; (the rest of the fields)
Doe Jarl C++ 3/4/05
Doe John Net+ 4/5/06

Would anyone be able to give an example chunk of VBA code to make this work?

Please let me know if you have questions or require additional information and as always, thank you for your valuable time/effort.

Mike

[twinnyfo]

Probably the best way to do this is to use a User-Defined Function that concatenate those fields into one. If you do a search on this forum for “MS Access VBA Concatenate” you may be able to find several that are useful. I couldn’t find the exact specific one I was looking for, but there are several that can point you in the right direction.

Hope this hepps!

[NeoPa]

Combining Rows-Opposite of Union may prove helpful.

[ADezii]

1. Placing a Full Name in a Field called [Name] is wrong for so many reasons, but that is probably a Topic for discussion at another time.
2. Sample Data used in Demo:
   

   ID    TName      Certs    Cert Date
   1    Doe Jane    CISSP    1 /2 /2003
   2    Doe Jane    SEC+     2 /3 /2004
   3    Doe Jarl    C++      3 /4 /2005
   4    Doe John    NET+     4 /5 /2006
   5    Doe Jane    MCSE     6 /23/2007
   6    Doe Jarl    PAER     12/12/2008
   7    Flintstone Fred        
    

3. Create the following Query which will list all 'UNIQUE' Names sorted alphabetically and call a Function within a Calculated Field:
   

   SELECT DISTINCT tblData.TName AS Employee_Name, fConcat([TName]) AS Certification
   FROM tblData
   ORDER BY fConcat([TName]);
    

4. Function Definition:
   

   Public Function fConcat(strName As String) As String
   Dim MyDB As DAO.Database
   Dim rstConcat As DAO.Recordset
   Dim strBuild As String
    
   Set MyDB = CurrentDb
    
   Set rstConcat = MyDB.OpenRecordset("SELECT * FROM tblDATA WHERE [TName] = '" & strName & "'", dbOpenForwardOnly)
    
   With rstConcat
     Do While Not rstConcat.EOF
       strBuild = strBuild & ![Certs] & " " & ![Cert Date] & ";"
         Debug.Print ![TName]
           .MoveNext
     Loop
   End With
    
   fConcat = Left$(strBuild, Len(strBuild) - 1)
    
   rstConcat.Close
   Set rstConcat = Nothing
   End Function
    

5. Results after Query Execution:
   

   Employee_Name    Certification
   Flintstone Fred     
   Doe Jarl    C++ 3/4/2005;PAER 12/12/2008
   Doe Jane    CISSP 1/2/2003;SEC+ 2/3/2004;MCSE 6/23/2007
   Doe John    NET+ 4/5/2006
    

6. Look at the Attachment, and should you have any questions feel free to ask.

[USTRAGNU1]

Thank you all for your quick replies! I can't open the demo.zip at work, but I will give this a try, thanks!

Mike