Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

How To

Concatentating a one to many relationship ino a single column by AWithers
Posted: 25 Apr 03 (Edited 29 Jun 03)

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
    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 & "'")

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


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

End Sub

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close