remove duplicates by clubbing together

[manju]

hi i have data as below
Order part
12343 WILD
13213 WELD
13213 TELD
13213 FRLD
53422 YEYR
53422 ERRW

i would like to remove duplicates by converting this to

Order part
12343 WILD
13213 WELD,TELD,FRLD
53422 YEYR,ERRW

 

[vanderghast]

That is a LIST-concatenation.

It can be done in many ways. One of them is to make a table, concats, two
fields, order, the primary key, and concat (varchar(255)), allowing nulls.
Fill that table with one record for each possible order value:

Step 1:

SELECT DISTINCT order, iif(false, "", null) AS concat INTO concats FROM
orders


The second step will produce the list-concatenation through an update query:

Step 2:

UPDATE concats INNER JOIN orders
ON concats.order = orders.order
SET concat = (concat + ", ") & part



The result is then in the table concats.


To begin anew, delete table concats and start at step 1.


That works only with Jet, not with MS SQL Server, though.


Vanderghast, Access MVP

 

[John W. Vinson]

hi i have data as below
Order part
12343 WILD
13213 WELD
13213 TELD
13213 FRLD
53422 YEYR
53422 ERRW

i would like to remove duplicates by converting this to

Order part
12343 WILD
13213 WELD,TELD,FRLD
53422 YEYR,ERRW

You can do this with the help of some VBA code:

http://www.mvps.org/access/modules/mdl0004.htm

 

[manju]

Many Thanks to Vanderghast this solution worked.

That is a LIST-concatenation.

It can be done in many ways. One of them is to make a table, concats, two
fields, order, the primary key, and concat (varchar(255)), allowing nulls.
Fill that table with one record for each possible order value:

Step 1:

SELECT DISTINCT order, iif(false, "", null) AS concat INTO concats FROM
orders


The second step will produce the list-concatenation through an update query:

Step 2:

UPDATE concats INNER JOIN orders
ON concats.order = orders.order
SET concat = (concat + ", ") & part



The result is then in the table concats.


To begin anew, delete table concats and start at step 1.


That works only with Jet, not with MS SQL Server, though.


Vanderghast, Access MVP