wanna duplicate fields in one cell

[catcoota]

Hullo everyone !
I am a new member & um happy to find this great forum about MS ACCESS.

Well, I have a question & i don't know how to have the solution, maybe using query or macro?

anyway, here is my table

I have a table that looks like this for example

LC LD
1 10
2 11
3 9
1 8
4 22
1 19
2 10
4 15

I wanna make a query ( or a macro or anything!) that makes the values of the same (LC) gets recorded in the same cell

LC LD
1 10, 8, 19
2 11, 10
3 9
4 22, 15


the information is dynamic, I mean i get the first table daily from the company & I need to reorganize it to look like the second table, but I don't know how to do this...


Any help is higly appreciated.


Thanks a lot

 

[Pat Hartman]

The first table is correctly structured. Converting the data to the second structure will simply cause you problems. Leave it as it is. The referenced article will show you how to create a report that shows the data as your second example.

How to concatenate items from the many side table.

 

[catcoota]

Hullo

Thanks a lot for ur reply
I have applied the lesson explained in the link you have given to me, but, I don't know why I get the column that is supposed to contain contanenated fields, i get it empty!! although i applied the lesson to the same file which is northwind!

did you follow the lesson and apply it on NWIND file and did it work with?

Hope u can help me !

 

[Pat Hartman]

I have not tried it myself but I have posted the link a number of times and others have gotten it to work. You might try searching for one of those posts to see if anyone had a similar problem. Look for -
concatenate and many
and my name to narrow your search.

 

[Jon K]

The procedures described in the article work (see DB attached).

As the report differs from a conventional one, its underlying principle or concept may be a little difficult to grasp at first.

 

[catcoota]

Pat Hartman


Thanks for ur support, i discovered that there is something wrong i made in the design phase which made this problem, (although everything else was just fine)! thanks a lot dear



Jon K


When i saw the design of ur report I thought that you did auto report, so i made the same thing and then everything worked fine! in the last time i did it using the wizard and it didn't work although i wrote everything right, it seems it is problem with the layout of the report? any way, thanks sooooooooooooooooooo much
I do appreciate ur help

 

[Rose412]

I need to concatenate a field from a many-side table. I followed the steps in the MS article and have successfully created the report.

My boss wants to have an Excel spreadsheet instead of a printed copy. I tried to export the report to Excel by choosing the menu File, Export. But the Excel file contained only the name of the field in A1 and nothing else. Can someone help me?

 

[Jon K]

The report you have created differs from an ordinary one as the Visible property of the Details band has been set to No. That's why you won't get any details in the exported Excel file.

Instead of using a report, you can use some VBA code to put the concatenated results in a temporary table and then export the temporary table to Excel.


I have attached a DB using catcoota's data as an illustration. The data are placed in the "OriginalData" table, to which I have added a RecordID field. The RecordID field is for ordering the LDs in the concatenated string (see SQL statement in the code). I have also added an empty table "TempTable" to hold the concatenated results.

When you click on the command button on the form, the results will be appended to the TempTable. The code is in the On Click Event of the command button:-

Private Sub cmdUpdate_Click()
   On Error GoTo Err_Handler
   
   Dim db As DAO.Database
   Dim rsOrig As DAO.Recordset
   Dim rsTemp As DAO.Recordset
   Dim SQL As String
   Dim ThisLC
   Dim LDs As String
   
   Set db = CurrentDb
   
   ' delete previous records from TempTable.
   db.Execute "Delete * from [TempTable]"
         
   ' open OriginalData and TempTable as recordsets.
   SQL = "Select * from [OriginalData]" & _
         " order by [LC],[RecordID]"
   
   Set rsOrig = db.OpenRecordset(SQL)
   Set rsTemp = db.OpenRecordset("TempTable")
   
   ' loop through original data to string the LDs.
   Do While Not rsOrig.EOF
     ThisLC = rsOrig![LC]
     LDs = ""
     Do While rsOrig![LC] = ThisLC
       LDs = LDs & ", " & rsOrig![LD]
       rsOrig.MoveNext
       If rsOrig.EOF Then
         Exit Do
       End If
     Loop
     ' remove leading comma and space.
     LDs = Mid(LDs, 3)
     
     ' update TempTable.
     rsTemp.AddNew
     rsTemp![LC] = ThisLC
     rsTemp![LD] = LDs
     rsTemp.Update
   Loop
   
   ' clean up
   Set rsOrig = Nothing
   Set rsTemp = Nothing
   Set db = Nothing
     
   ' display message.
   MsgBox "Updated TempTable."
   Exit Sub
   
Err_Handler:
   MsgBox Err.Description
   
End Sub

After the TempTable is updated, you can right-click on it to export to Excel.

Hope you can adapt the code to suit your needs.


Note. The attached DB is in Access 97 format, so DAO was used. If you write the code in Access 2000 or 2002, you must make a reference to DAO (when the code window is open, choose Microsoft DAO 3.6 Object Library from the available list.)


(To open the attached DB in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)

 

[Rose412]

Thanks, Jon K. It worked.

My database already has some code in ADO. Can I change your code to run in ADO? If it is possible, I don't want to use both ADO and DAO. Thanks again.

 

[Jon K]

The ADO code is quite similar.

Private Sub cmdUpdateADO_Click()
   On Error GoTo Err_Handler
   
   Dim cnn As ADODB.Connection
   Dim rsOrig As ADODB.Recordset
   Dim rsTemp As ADODB.Recordset
   Dim SQL As String
   Dim ThisLC
   Dim LDs As String
   
   Set cnn = CurrentProject.Connection
   
   ' delete previous records from TempTable.
   cnn.Execute "Delete * from [TempTable]"
  
   ' open OriginalData and TempTable as recordsets.
   SQL = "Select * from [OriginalData]" & _
         " order by [LC],[RecordID]"
   
   Set rsOrig = New ADODB.Recordset
   rsOrig.Open SQL, cnn, adOpenStatic, adLockReadOnly
   
   Set rsTemp = New ADODB.Recordset
   rsTemp.Open "TempTable", cnn, adOpenDynamic, adLockOptimistic
   
   ' loop through original data to string the LDs.
   Do While Not rsOrig.EOF
     ThisLC = rsOrig![LC]
     LDs = ""
     Do While rsOrig![LC] = ThisLC
       LDs = LDs & ", " & rsOrig![LD]
       rsOrig.MoveNext
       If rsOrig.EOF Then
         Exit Do
       End If
     Loop
     ' remove leading comma and space.
     LDs = Mid(LDs, 3)
     
     ' update TempTable.
     rsTemp.AddNew
     rsTemp![LC] = ThisLC
     rsTemp![LD] = LDs
     rsTemp.Update
   Loop
   
   ' clean up
   Set rsOrig = Nothing
   Set rsTemp = Nothing
   Set cnn = Nothing
     
   ' display message.
   MsgBox "Updated TempTable."
   Exit Sub
   
Err_Handler:
   MsgBox Err.Description
   
End Sub

 

[Rose412]

That worked perfectly. Thanks again.