Is there any sql statement to concatenate strings using group by?

For example, the table structure:

Order No. Items
001 Apple
001 Orange
002 Apple

I would like to SELECT the table group by Order No.:

Order No. Items
001 Apple Orange
002 Apple

thanks
Joe

i have no idea, but i can write a function to do it:SELECT distinct order, concat([items]) FROM table
Function concat(ordernum As Long)

Dim mystring As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM table WHERE [order] = " & ordernum)

With rs
.MoveFirst
Do Until .EOF
mystring = mystring & " " & !items
.MoveNext
Loop

concat = mystring

End With

End Function