SQL or VBA code to loop through two columns of data

[limey]

I have a table with 196000 rows (product code and related product codes). A product has one to many link product codes that I wish to display in one row. The columns are:

ProductCode LinkProduct
000507 000123
000507 000456
000507 000789
000508 123456
000508 654321
000508 879456
000508 123789
etc etc

I want to change this data to:

ProductCode LinkProducts
000507 000123,000456,000789
000508 123456,654321,879456,123789
etc

I've spent days trying to work this out in Access but can't do it. Would somebody please help me with this task.

[NeoPa]

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Please don't use any TABs in your formatted layout as this will not display correctly - Spaces only for layout.
Table Name=[tblStudent]

Field           Type        IndexInfo
StudentID       AutoNumber  PK
Family          String      FK
SName           String
University      String      FK
Mark            Numeric
LastAttendance  Date/Time

[limey]

Field Name Data Type
ProductCode Text
LinkProductCode Text

[ADezii]

@limey
You still did not give us much to work on, so I filled in a few missing blanks:

1. tblProductCodes contains the Field [ProductCode] and this Field is the Primary Key {TEXT}.
2. tblLinkedProductCodes also contains the [ProductCode] Field {TEXT}, as well as a [LinkProductCode] Field.
3. There is a 1 to MANY Relationship between tblProductCodes and tblLinkedProductCodes based on the [ProductCode] Field.
4. Copy-and-Paste the following 'Public' Function to a Standard Code Module:
   

   Public Function fGenerateLinkProductCodes(strProductCode As String) As String
   Dim MyDB As DAO.Database
   Dim rstProd As DAO.Recordset
   Dim strSQL As String
   Dim strBuild As String
    
   Set MyDB = CurrentDb
    
   strSQL = "Select tblLinkedProductCodes.[LinkProductCode] From tblLinkedProductCodes Where " & _
            "tblLinkedProductCodes.[ProductCode] = '" & strProductCode & "';"
    
   Set rstProd = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
   With rstProd
     If Not .BOF And Not .EOF Then     'Related Records exist
       Do While Not .EOF
         strBuild = strBuild & ![LinkProductCode] & ", "
           .MoveNext
       Loop
         fGenerateLinkProductCodes = Left$(strBuild, Len(strBuild) - 2)
     Else
       fGenerateLinkProductCodes = "No Related Records for " & strProductCode
     End If
   End With
    
   rstProd.Close
   Set rstProd = Nothing

5. Create the following Query:
   

   SELECT tblProductCodes.ProductCode, fGenerateLinkProductCodes([ProductCode]) AS LinkProducts
   FROM tblProductCodes;

6. Query Results:
   

   ProductCode    LinkProducts
   000507         000123, 000456, 000789
   000508         123456, 654321, 879456, 123789
   000839         No Related Records for 000839

7. This is a code-based approach utilizing a Public Function in a Query. There is probably a more efficient SQL based solution which one of the other guys will come up with, so stay tuned.
8. Any questions, feel free to ask.

[limey]

Thank you very much ADezii. Your solution is exactly what I was hoping for and I can use this code as a starting point for doing similar things with Access data in the future.

Have a great day and once again thank you for your help.

[NeoPa]

This isn't remotely straightforward Limey. The main reason for this is it goes against the concepts of a relational database.

Furthermore (and probably for this very reason) I don't believe there is a simple SQL solution.

That's a long way from saying we haven't had similar requests before mind you (Sorry. My earlier confusion was not the question, but simply my overlooking it could be as simple as you stated it).

A solution I prepared earlier (probably similar in many respects to ADezii's) is Producing a List from Multiple Records.

NB. There is a limit of 255 chars that a string field (not variable in VBA - just a string field in a recordset) can take.

[ADezii]

@limey
You are quite welcome.

[ADezii]

@NeoPa
Just for curiosity, NeoPa. No data or structural changes are being made, Referential Integrity is not being violated, and the Database itself is not being altered in any manner. All that is happening is that data is being displayed in a Read Only Query for observation purposes. I honestly do not see this as a violation of the concepts of a Relational Database. Just my simple point of view.

[ChipR]

The OP said "I want to change this data to:" and since 196,000 rows were mentioned, I believe the intent is to store the data this way to reduce the number of rows. I would caution against that, since once you do this you can't actually use the data.

[ADezii]

Sorry ChipR and NeoPa, but I must have totally missed that. In that case, you of course, are 100% correct.

[NeoPa]

@ADezii
I find myself struggling mightily to express why I feel this is an issue, at least for the purist.

The words don't come easily, and if I'm honest, I don't think a discussion of the nitty-gritty of this would prove very helpful. I certainly couldn't put forward a compelling argument.

Agree to disagree seems fine :)

PS. Absolutely no need to apologise. Your point stands alone, regardless of what the OP in this instance requires.