Tricky Combination

[Ant]

Hi All,

I am trying to use a query to combine various values in a
field into a new field for each corresponing ID.

Basically I have ID field(not Primary Key, Duplicates
allowed), Field1 (relative values) and Field2 (New field).

There may be many instances of ID but no more than 10.
each instance of ID will have a single uniqe text value in
field1. But as before there could be 1 to 10 of these. I
want to combine all the values in field1 into field2 so
that Field2 shows all values in field1 for each ID in
field2.

A bit confusing I know but any help will be greatly
appreciated.

I have trried using a loop in VBA to write the new value
in but it keeps crashing my PC. I am deeling with about
3million records

Ant

 

[Michel Walsh]

Hi,


You have a small example on small subset of data (all text, no attachment
please) ?


Vanderghast, Access MVP

 

[Ant]

I tried to send you a mail but your address is not valid.
Here is the mail and a small example of the data.

Attached is an example of the data I am using in a plain
text, coma delimited format.

The first 2 columns is what I have and the third is what I
am hoping to get (although only one record per 'ID' is
required). I got this result using Update SQL statement in
a VBA loop but this would eventually crash my PC and that
is why I want to know if there is a way to do it pureley
in a query or SQL.

Any help you can offer will be greatly appreciated.

Ant

100000425,1,1
100000431,1,167
100000431,7,167
100000431,6,167
100000431,6,167
100000431,7,167
100000438,1,123811
100000438,2,123811
100000438,3,123811
100000438,3,123811
100000438,8,123811
100000438,11,123811
100000480,1,123
100000480,2,123
100000480,3,123
100000480,3,123
100000491,1,123
100000491,2,123
100000491,3,123
100000491,3,123
100000491,3,123
100000504,1,1237
100000504,2,1237
100000504,3,1237
100000504,3,1237
100000504,7,1237
100000515,1,13
100000515,3,13
100000519,1,1237
100000519,2,1237
100000519,3,1237
100000519,3,1237
100000519,7,1237
100000575,1,123
100000575,2,123
100000575,3,123
100000575,3,123
100000575,3,123
100000587,1,13910
100000587,3,13910
100000587,10,13910
100000587,9,13910
100000587,9,13910
100000587,9,13910
100000594,1,123
100000594,2,123
100000594,3,123
100000605,1,123
100000605,2,123
100000605,3,123
100000605,3,123
100000618,1,123
100000618,3,123
100000618,2,123
100000629,1,12371113
100000629,2,12371113
100000629,3,12371113
100000629,3,12371113
100000629,11,12371113
100000629,11,12371113
100000629,7,12371113
100000629,13,12371113
100000631,1,12
100000631,2,12

 

[Michel Walsh]

Hi,


That would be a LIST( ) aggregate, which, unfortunately, does not exist
neither in Jet, neither in MS SQL Server. On the other hand, we can
"simulate" it, with a few steps. I assume the initial table has two fields,
f1 and f2.


- Create a temp table with the DISTINCT groups

SELECT DISTINCT f1, iif(false, "", NULL ) As f2 INTO temp FROM myTable

- Create a temp table where you won't have duplicated groups

SELECT DISTINCT f1, CStr(f2) As f2 INTO tempA FROM myTable


- make the lists

UPDATE temp INNER JOIN tempA ON temp.f1=tempA.f1
SET temp.f2=(temp.f2 + ", " ) & tempA.f2


The result is in temp. Note that you have to DROP TABLE temp and tempA if
they exist before creating them with the SELECT INTO.



Hoping it may help,
Vanderghast, Access MVP