Transposing Columns to Rows

[mk]

Hi all,
I have been trying to figure this out, but with no luck.
What I have is a table with data:

Letter String
A STR1
A STR2
A STR3
A STR4
B STR5
B STR6
etc
What I would like to get it into a table, but organized this way:

Letter Strings
A STR1, STR2, STR3, STR4
B STR5, STR6
etc
I have looked at pivot tables and Crosstab queries, but can’t figure
this out. I was playing with some code, but couldn’t decide if I need
a For Each Loop, or a While Not.EOF.
Any help would be greatly appreciated.

Thank YOu,
Mike

 

[Tom Wickerath]

Hi Mike,

For display purposes only, you might try the method shown here:

Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm

However, I definately recommend against attempting to store such
concatenated results in a table. Doing so would violate database
normalization, and generally add a lot of misery to your future work with
this data (for example, if you later need to create a report grouped by the
values shown in your String column, or simply counting the number of
occurances of a particular string value).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John Spencer]

Here are three examples on how to concatenate field values from multiple rows.

Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mk]

Thanks John and Tom for all your help. I got it to work and this will
be invaluable for the future.
I had tried to look in Access Web, but wasn't even sure what I was
looking for or what to call it.
Thanks again and have a great Holiday!

Mike