Solved

Need to combine duplicate values into one field

Posted on 2011-03-14
3
315 Views
Last Modified: 2012-06-27
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
Comment
Question by:fordraiders
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

download the sample db
0
 
LVL 12

Accepted Solution

by:
ErezMor earned 500 total points
ID: 35133630
Sub x1()
Dim rs As New ADODB.Recordset
Dim LastParent As String, strChildren As String

rs.Open "Select * From table1 Order By fldParent", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
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

by:fordraiders
ID: 35134225
perfect Thanks !  erezmor
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question