Access: Grouping in Reports -

i have report in access database containing multiple fields, e.g., looks follows:

name john
number 18
place somewhere

name john
number 19
place somewhere

name john
number 20
place somewhere

name bogo
number 30
place somewhere

john can have multiple numbers. can see, "john" listed multiple times, each different number. 1 detail row name "john", following:

name john
number 18; 19; 20
place somewhere

name bogo
number 30
place somewhere

how can achieve this?

the solution use vba code in access module "string together" repeated values ("number" in case). general form of code is:

option compare database option explicit  public function listchildnames(parentid long) string dim cdb dao.database, rst dao.recordset, rtn string const separator = "; " set cdb = currentdb set rst = cdb.openrecordset( _         "select [childname] [childtable] " & _         "where id=" & parentid, _     dbopensnapshot) rtn = "" while not rst.eof     rtn = rtn & rst![childname] & separator     rst.movenext loop rst.close set rst = nothing set cdb = nothing if len(rtn) > 0     rtn = left(rtn, len(rtn) - len(separator))  '' trim trailing separator end if listchildnames = rtn end function 

(you have tweak table , column names match table structures.)

then, in record source report, instead of using...

select [name], [number] ... ... 

...you use like...

select [name], listchildnames([name]) numbers ... ... 

...to retrieve [name] , (concatenated list of) [number] values in single row.