Crosstab Query - Multiple Value Responses

  • Thread starter Thread starter Blondee
  • Start date Start date
B

Blondee

I've created a crosstab from a table with data laid out like this:
Group Section Title
A One John - Manager
A One Joe - Analyst
A Two Jane - Director
B Three Sue - Director
B One Bill - Manager
C Two Bob - Analyst
C Two Tom - Director
C Three Jim - President
D One Linda - Analyst
D Two Lucy - Manager

I'd like to end up with a cross tab like this (you may have to use your
imagination on layout since couldn't paste a picture) basically trying to get
multiple items to display in the value field.
Group One Two Three
A John - Manager
Joe - Analyst Jane - Director
B Bill - Manager
Sue - Director
C Bob - Analyst
Tom - Director
Jim - President
D Linda - Analyst Lucy - Manager

In my data above, I have two people in Group A and Section One.
I'm able to get the first item to appear using First, but can't get all of
them to appear. Is this possible?
Thanks for any assistance.
 
Try this --
TRANSFORM First(TableA.[Title]) AS FirstOfTitle
SELECT TableA.[Group], [Group] & [Title] AS Expr1
FROM TableA
GROUP BY TableA.[Group], [Group] & [Title]
PIVOT TableA.[Section] IN("One", "Two", "Three");
 
Thanks - I'll give it a try.

KARL DEWEY said:
Try this --
TRANSFORM First(TableA.[Title]) AS FirstOfTitle
SELECT TableA.[Group], [Group] & [Title] AS Expr1
FROM TableA
GROUP BY TableA.[Group], [Group] & [Title]
PIVOT TableA.[Section] IN("One", "Two", "Three");

--
Build a little, test a little.


Blondee said:
I've created a crosstab from a table with data laid out like this:
Group Section Title
A One John - Manager
A One Joe - Analyst
A Two Jane - Director
B Three Sue - Director
B One Bill - Manager
C Two Bob - Analyst
C Two Tom - Director
C Three Jim - President
D One Linda - Analyst
D Two Lucy - Manager

I'd like to end up with a cross tab like this (you may have to use your
imagination on layout since couldn't paste a picture) basically trying to get
multiple items to display in the value field.
Group One Two Three
A John - Manager
Joe - Analyst Jane - Director
B Bill - Manager
Sue - Director
C Bob - Analyst
Tom - Director
Jim - President
D Linda - Analyst Lucy - Manager

In my data above, I have two people in Group A and Section One.
I'm able to get the first item to appear using First, but can't get all of
them to appear. Is this possible?
Thanks for any assistance.
Click to expand...
Click to expand...
 
Back
Top