By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Translate (convert, transform) multiple rows to single column

P: 1
I am using Access I have this:

TABLE A
Slide Position GO ID
1
2
3
4 GO:0000166
4 GO:0000775
4 GO:0000795
4 GO:0000800
4 GO:0003677
5
6
7
8 GO:0042802
9
10 GO:0003779
10 GO:0005509


And would like to get this
TABLE B
Slide Position GO ID
1
2
3
4 GO:0000166, GO:0000775, GO:0000795, GO:0000800, GO:0003677
5
6
7
8 GO:0042802
9
10 GO:0005509, GO:0005856, GO:0007010, GO:0016020, GO:0051016


I already have created a table with "Slide Position" as primary key (TABLE C), but I don't get something similar to Table B

TABLE C with single column (and primary key)
Slide Position
1
2
3
4
5
6
7
8
9
10

Is there any way to create TABLE B?

thanks
Mar 15 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
For this you'll need a function to concatenate the different values into a string. This function can be stored in the modules section:

Expand|Select|Wrap|Line Numbers
  1. Function fncConcat(lngKeyField As Long) As String
  2.  
  3. Dim RS As DAO.Recordset
  4.  
  5. ' init function string
  6. fncConcat = ""
  7. Set RS = CurrentDb.OpenRecordset("select [GO ID] from [TAble A] where [Slide Position]=" & lngKeyField)
  8. If RS.EOF And RS.BOF Then
  9.    Exit Function
  10. End If
  11. RS.MoveFirst
  12. While Not RS.EOF
  13.    fncConcat = fncConcat & ", " & RS.fields("GO ID")
  14.    RS.MoveNext
  15. Wend
  16. ' remove first ", "
  17. fncConcat = Mid(fncConcat, 3)
  18.  
  19. End Function
  20.  
Now in a query use:

select distinct [Slide Position] , fncConcat([Slide Position]) from [Table A];

Nic;o)
Mar 17 '07 #2

NeoPa
Expert Mod 15k+
P: 31,347
You could try looking in (Combining Rows-Opposite of Union ), where something similar is discussed.
Mar 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.