MS Access and rotate data

Is there a way to rotate data in SQL or other mechanism in MS Access?  For example, say I have 3 records of data, say text string of colors  - "blue", "red", "green".   Is there a way to return a concatenation of all three colors in one string in one record?
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
0
 
MacroShadowCommented:
You can use a recordset. This example will replace the last record with the concatenated value.
Sub Demo()
	Dim rs As DAO.Recordset
	Dim str As String

	Set rs = CurrentDb.OpenRecordset("table")

	rs.MoveFirst
	Do Until rs.EOF
		rs.Edit
		str = str & " " & rs!ColorFieldName
		rs.MoveNext
	Loop
	rs!ColorFieldName = str
	rs.Update
	rs.Close
	Set rs = Nothing
End Sub

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Use the following to flatten row data:

https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Access does have a crosstab (PIVOT), but nothing to flatten built-in.

Jim.
0
 
HLRosenbergerAuthor Commented:
Thanks for all the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.