Concatenate the records with same ID

T

Tim

Hi folks,

I need a help on my query. I tried to concatenate the records which have
the same ID. The following is the data in my table (Table1):

ID Value
1 a
1 b
2 g
2 h
2 y
3 d
4 p
4 o

I need the output of my query like the following:

ID Value
1 a, b
2 g, h, y
3 d
4 p, o

Could anyone show me how to do it?

Thanks in advance.

Tim
 
K

KARL DEWEY

This will work if you only have two Value to work with but I see more than
two in some cases of your example.

Use two queries. I named the first one Query58 from Table20 so you can
change accordingly.

SELECT [RecID] & [SubID] AS Identification, First(Table20.Value) AS
FirstOfValue, Last(Table20.Value) AS LastOfValue
FROM Table20
GROUP BY [RecID] & [SubID];


SELECT Query58.Identification,
IIf([FirstOfValue]=[LastOfValue],[FirstOfValue],[FirstOfValue] & ", " &
[LastOfValue]) AS Type
FROM Query58;
 
M

Michel Walsh

Hi,


In two steps. First, create a temp table that will get the two fields, and
unique ID (drop the table if it already exists, before)


SELECT DISTINCT id, iif(false, "", null) As concat INTO temp FROM mytable



Finally, create the various concatenations with a SET over a standard INNER
join:


UPDATE temp INNER JOIN myTable ON temp.id=mytable.id
SET temp.concat = ( temp.concat + ", " ) & mytable.value



The result is now in the table temp.


Hoping it may help,
Vanderghast, Access MVP
 
G

Gigi

Michel,

I have used your routine and it has done exactly what I wanted.. howver the
field I have joined is a cd track and the end result of all 20 tracks has
been truncated, I changed the format from text to memo in the temp file but
it has still truncated, can you help please?

many thanks
 
M

Michel Walsh

Hi,


The query update can only handle 255 chars max, in a field, even if you turn
it into a memo, before being "stored", it has to be handled internally in a
standard field.


Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


Have you tried the solution proposed by Duane?


Hoping it may help,
Vanderghast, Access MVP
 
Top