Please Help With Concatenation Query

[ErikRP]

I have a table that I need to query that looks something like this:

ID Type Product ID
123 Health 323
123 Health 424
123 Dental 424
124 Health 323
125 Dental 323

What I need to see is something like this:

ID Type Product ID
123 Health 323 + 424
123 Dental 424
124 Health 323
125 Dental 323

In other words, I need to have the Product ID field concatenate for like ID and Type. I know it would likely involve writing some code but I am a total beginner in that respect and I don't know where to even begin. I could really use some help. Of course I need this as soon as possible!

To make matters worse (well, maybe it does), I am using Access 97.

Thanks so much!!!

 

[EMP]

I know it would likely involve writing some code

Yes, you do need to write some VBA code. The code depends much on the structure of the table.

What is the table name?
What are the data types of the ID and Product ID fields? Are they numbers or text?

^

 

[ErikRP]

The table name is Analysis. All of the fields are text strings. (I simplified my example but the data in each field is alphanumeric.)

Thanks EMP!

 

[EMP]

It would be more efficient to update a temporary table than do concatenation in a query.


You can first create a temporary table "tblTemp" with the fields: ID, Type, Product ID. Make the Product ID field long enough to store concatenated strings and set the field Index to No (i.e. not indexed by Product ID.)

Then open a new form in Design view. Put a command button on the form and change its name to cmdUpdate using the Properties window.

Type/paste the following code in the On Click event of the command button:-

Private Sub cmdUpdate_Click()

   Dim db As DAO.Database
   Dim rsData As DAO.Recordset
   Dim rsTemp As DAO.Recordset
   Dim SQL As String
   Dim sID As String
   Dim sType As String
   Dim sProductIDs As String
   
   Set db = CurrentDb()
   
   '--empty temp table.
   SQL = "Delete * from [tblTemp]"
   db.Execute SQL
   
   '--open temp table as recordset.
   Set rsTemp = db.OpenRecordset("tblTemp")
   
   '--open data table as recordset.
   SQL = "Select [ID], [Type], [Product ID]" & _
         " from [Analysis]" & _
         " Order by [ID], [Type], [Product ID]"
   Set rsData = db.OpenRecordset(SQL)
      
   '--loop through data recordset.
   Do While Not rsData.EOF
      sID = rsData![ID]
      sType = rsData![Type]
      sProductIDs = ""
      '--concatenate product IDs.
      Do While rsData![ID] = sID And rsData![Type] = sType
         sProductIDs = sProductIDs & " + " & rsData![Product ID]
         rsData.MoveNext
         If rsData.EOF Then
            Exit Do
         End If
      Loop
      '--update temp recordset.
      rsTemp.AddNew
      rsTemp![ID] = sID
      rsTemp![Type] = sType
      rsTemp![Product ID] = Mid(sProductIDs, 4)
      rsTemp.Update
   Loop
   '--clean up.
   Set db = Nothing
   Set rsData = Nothing
   Set rsTemp = Nothing
   
   '--display message.
   MsgBox "Updated temporary table 'tblTemp'"

End Sub

Save the form.

Open the form and click on the command button to run the code.

^

 

[ErikRP]

THAT'S PERFECT!!!

Thanks so much - that produced exactly the kind of data I was needing! I appreciate that so much!!!