Concatentating a one to many relationship ino a single column

If you have data that looks like this
key datafield
1 X
2 X
2 Y
3 Z
4 X
4 Y
4 Z

How do you make it looks like this
1 X
2 X,Y
3 Z
4 X,Y,Z


It is not straightforward to represent a one to many relationship in a standard query without using VBA

Here is some code that can be used to insert data into a temporary table (only tested with ACCESS 97)

in this code 'project_nbr_store_list' is the table that contains the data 'project_nbr' the key and 'store' the datafield.

Many thanks to Jim deGeorge who took orginal code I created and tested/tweaked it and suggested it was worthy of a FAQ.

Please note if your key field is numeric then you will need to change the syntax of the sql in the line which starts db.execute

-------------------
Private Sub Command0_Click()

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim vStore As String ' string to contain list of stores

Set db = CurrentDb

'clear out TempTable
db.Execute "DELETE TempTable.* FROM TempTable;"

'first of all insert list of projects into table TempTable
db.Execute ("insert into TempTable (project_nbr) select distinct project_nbr
from project_nbr_store_list")

Set rs = db.OpenRecordset("select distinct project_nbr from TempTable")
'outer loop containing list of project numbers

Do Until rs.EOF = True
'for each project number create a string of stores
vStore = ""

Dim sql As String
sql = "select store from project_nbr_store_list where project_nbr ='" &
rs!project_nbr & "'"
'select list of stores for current project number

Set rs1 = db.OpenRecordset(sql)
'inner loop to create a concatenated string of stores

Do While rs1.EOF = False
vStore = vStore + ", " + rs1!store
rs1.MoveNext
Loop

If Len(vStore) > 0 Then
'trim off leading comma
vStore = Right(vStore, Len(vStore) - 1)

'update project list with store string
db.Execute ("UPDATE TempTable SET store = '" + vStore + "' WHERE
project_nbr = '" & rs!project_nbr & "'")

Else
'dont bother with update
End If
'move to next project number

rs.MoveNext
Loop

'release the memory
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub