How to perform concatenate operation in ms access vba using sql query? -

How to perform concatenate operation in ms access vba using sql query? -

i have below mentioned table in ms access called [info].

ab jde quantity po smp ind ab7049 15 f258738od 10176 ind am7619 21 f258738od 10176 ind as9147 20 f258738od 10176 aru am7619 8 f118215og 10176 aru am7619 16 f118215og 10176 aru am7619 8 f118215og 10176 aru am7619 12 f258210od 10176 aru am7619 24 f258210od 10176 aru am9147 120 f257894od 10176 aru am9147 102 f257894od 10176 aru as9147 18 f257905od 10176 aru as9147 30 f257905od 10176

here want perform concatenation , summing operation below mentioned table.

ab jde quantity po smp ind ab7049, am7619, as9147 56 f258738od 10176 aru am7619, am9147, as9147 338 f118215og, f258210od, f257894od, f257905od 10176

here macro needs concatenate unique jde , unique po , needs sum qunatity related jde , po. smp columns primary key.

please help me in issue.

first smp doesn't seem primary key.

my approach utilize vba function concenated strings , rest in sql.

vba function:

public function getconcvalues(byval ptablename string, byval pfieldname string, byval psearchcolumn string, byval psearchvalue string) string dim rs dao.recordset dim ret string dim sql string ' prepare sql sql = "" sql = sql & "select distinct [" & pfieldname & "] " sql = sql & " [" & ptablename & "] " sql = sql & " [" & psearchcolumn & "] = '" & psearchvalue & "' " sql = sql & "order [" & pfieldname & "]" ' initialize homecoming value ret = "" ' retrieve unique values based on search criterias set rs = currentdb.openrecordset(sql) until rs.eof ' add together content homecoming value ret = ret & rs.fields(0) & ", " rs.movenext loop ' trim lastly comma if requiered if len(ret) > 0 ret = left(ret, len(ret) - 2) end if ' cleanup rs.close set rs = nil ' homecoming result getconcvalues = ret end function

and can utilize in sql:

select ab, getconcvalues("info","jde","ab",[ab]) jde, sum(quantity) sumofquantity, getconcvalues("info","po","ab",[ab]) po, smp info grouping ab, getconcvalues("info","jde","ab",[ab]), getconcvalues("info","po","ab",[ab]), smp;

sql vba ms-access