Aggregating string values

[news.microsoft.com]

Hello All,

I have table with two fields,

Field ID1 can repeat but not ID2. ID2 is unique primary key
Both have string data type

Table T1
----------
ID1 ID2
x x1
x x2
x x3
y y1
y y2


Now I want to query something like
Select ID1, SUM_STRING(ID2) from T1 Group by ID1

but as you know, it is not possible to get sum of string

I want result as
x x1,x2,x3
y y1,y2

I am stuck
Is there anyway, this could be achieved?????

Best Regards,
Pravin

 

[Guest]

If no body will give you an answer how to do it with a query then you can use
some code, inserting data into a temp table, then you can use that table

I hope it works

Function aaa()
dim MyDB as database, MyRec1 as recordset, MyRec2 as recordset, CurrId as
string

set MyDB=codedb
docmd.runsql "delete * from temptable"
set MyRec1 = MyDB.openrecordset("Select * From TempTable")
set MyRec2 = MyDB.openrecordset("Select * From MyTable order by ID1")
CurrId = MyRec2!ID1
MyRec1!ID1= CurrId
MyRec1.AddNew
while not MyRec2.eof
If CurrId = MyRec2!ID1 then
MyRec1!ID2= MyRec2!ID2 & ","
Else
MyRec1!ID2= left(MyRec2!ID2 , len(MyRec2!ID2 )-1)
MyRec1.Update
MyRec1.AddNew
MyRec1!ID1= CurrId
End if
wend
MyRec1.Update
End Function

 

[Van T. Dinh]

See The Access Web article:

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