New Column for Row Data

[Mike]

Hi, I wonder if someone could help me out with this...

I have query results that look like this right now.

Results:

ID GroupNumber
1 5
1 6
2 5
3 4

I need to create another column combining the GroupNumber
for the ID.

Desired Results:

ID GroupNumber GroupDisplay
1 5 5,6
1 6 5,6
2 5 5
3 4 4

 

[Michel Walsh]

Hi,



Make first a temporary table:


SELECT DISTINCT ID, iif(false, "", null) INTO temp(ID, Concat FROM myTable

Next, run the following update:


UPDATE temp INNER JOIN myTable ON temp.ID=myTable.ID
SET Concat=(Concat + ", ") & myTable.GroupNumber



Finally, run

SELECT myTable.*, temp.Concat
FROM myTable INNER JOIN temp ON myTable.ID=temp.ID


Indeed, the first query make a temp table collecting each possible values of
ID just once, and define a field, concat, empty. The update makes the
required concatenation, updating concat with itself, each time it appears in
the join. Finally, the result you want can be read, in clear, with an inner
join linking the initial group id with the computed concatenation, through
the matching group id.


Hoping it may help,
Vanderghast, Access MVP

 

[Mike]

Thanks Michel,
That works great!!!

-----Original Message-----
Hi,



Make first a temporary table:


SELECT DISTINCT ID, iif(false, "", null) INTO temp(ID, Concat FROM myTable

Next, run the following update:


UPDATE temp INNER JOIN myTable ON temp.ID=myTable.ID
SET Concat=(Concat + ", ") & myTable.GroupNumber



Finally, run

SELECT myTable.*, temp.Concat
FROM myTable INNER JOIN temp ON myTable.ID=temp.ID


Indeed, the first query make a temp table collecting each possible values of
ID just once, and define a field, concat, empty. The update makes the
required concatenation, updating concat with itself, each time it appears in
the join. Finally, the result you want can be read, in clear, with an inner
join linking the initial group id with the computed concatenation, through
the matching group id.


Hoping it may help,
Vanderghast, Access MVP






.