Solved

# Need to combine duplicate values into one field

Posted on 2011-03-14
320 Views
Access 2003

vba routine needed:

I have a table with 2 fields:

I need a new table or query to show th results below:

tblParentChild
fldParent -text
fldChild - text

fldParent      fldChild
D0001      2XKT1
D0001      2XKT2
D0001      2XKT6
D0001      2XKT5
D0001      2XKT4
D0001      2XKT3
D0002      2XKR7
D0002      2XKR8
D0002      2XKR6
D0002      2XKR5
D0002      2XKR4
D0002      2XKR9

I need it to look like this
fldParent                     fldChild
D0001            2XKT1 : 2XKT2 : 2XKT6 : 2XKT5 : 2XKT4 : 2XKT3
D0002            2XKR7 : 2XKR6 : 2XKR5 : 2XKR4 : 2XKR9

Thanks
fordraiders

parentchild.png
0
Question by:fordraiders
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 120

Expert Comment

ID: 35133592
see the function ConcatThem and the query in this similar thread

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26833591.html?#a34933741

0

LVL 12

Accepted Solution

ErezMor earned 500 total points
ID: 35133630
Sub x1()
Dim LastParent As String, strChildren As String

While Not rs.EOF
If LastParent <> rs!fldParent Then
If LastParent <> "" Then
DoCmd.RunSQL "Insert Into Table2 (fldParent, frlChild) Values('" & LastParent & "','" & Left(strChildren, Len(strChildren) - 3) & "');"
End If
LastParent = rs!fldParent
strChildren = rs!fldChild & " : "
Else
strChildren = strChildren & rs!fldChild & " : "
End If
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub
0

LVL 3

Author Closing Comment

ID: 35134225
perfect Thanks !  erezmor
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
###### Suggested Courses
Course of the Month4 days, 16 hours left to enroll