Returning SQL rows with data concated per row in access -

Returning SQL rows with data concated per row in access -

in access have table

id| rooms | notes| 78| 234| | 3 | 231| key | 78| 195| | 3 | 164| |

i want sql query take id , combine them 1 row each like

78 -> 234,195 3->231, 164 -> key

i want combine rows in query no new table

unfortunately you'll have build function this, thankfully access supports utilize of vba functions within of sql query.

for illustration function concatenate rim's based on given id follows:

public function myrooms(lngid variant) variant dim rstrooms dao.recordset if isnull(lngid) exit function set rstrooms = "select rooms tblbookings id = " & lngid while rstrooms.eof if myrooms <> "" myrooms = myrooms & "->" myrooms = myrooms & rstrooms!rooms rstrooms.movenext loop rstrooms.close end function

now query can this:

select id, myrooms([id]) rooms, notes table.

you can create identical function much same notes column, , 1 time again place in above query.

sql ms-access