crosstab - multiple values required

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

Blondee

Would like to create a crosstab query where multiple values are returned for
each row/column heading intersection. Have data something similar to this:
Group Section Name Title
A ONE BOB DIRECTOR
A ONE SUE MANAGER
A TWO JANE ANALYST
B THREE BILL ANALYST
B TWO JOE VP
C THREE SALLY MANAGER
C THREE SUZY DIRECTOR
C ONE JIM MANAGER

Would like a crosstab query like this with multiple values at intersections:
ONE TWO THREE
A BOB DIRECTOR
SUE MANAGER JANE ANALYST
B JOE VP BILL
ANALYST
C JIM MANAGER SALLY MANAGER

SUZY DIRECTOR

Using a crosstab and the only option for returning a text value are first
and last, but nothing to return all values. Any thoughts out there?
Thanks for the assistance.
Apologies if this is posted twice, did not appear that my first post was
saved.
 
Break up the Name Title field and use Title as a row value. Of course there
might still be problems if there are multiple ANALYSTs in A - One for example.
 
Thanks for the assistance -- I will give it a try.

Duane Hookom said:
You should be able to use the results of the generic concatenate function
found at
http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16.html as the Value in a crosstab.

TRANSFORM First(Concatenate("SELECT [Name Title] FROM tblBlondee WHERE
[Group] = '" & [Group] & "' AND Section ='" & [Section] & "'")) AS Expr1
SELECT tblBlondee.Group
FROM tblBlondee
GROUP BY tblBlondee.Group
PIVOT tblBlondee.Section;
--
Duane Hookom
Microsoft Access MVP


Blondee said:
Would like to create a crosstab query where multiple values are returned for
each row/column heading intersection. Have data something similar to this:
Group Section Name Title
A ONE BOB DIRECTOR
A ONE SUE MANAGER
A TWO JANE ANALYST
B THREE BILL ANALYST
B TWO JOE VP
C THREE SALLY MANAGER
C THREE SUZY DIRECTOR
C ONE JIM MANAGER

Would like a crosstab query like this with multiple values at intersections:
ONE TWO THREE
A BOB DIRECTOR
SUE MANAGER JANE ANALYST
B JOE VP BILL
ANALYST
C JIM MANAGER SALLY MANAGER

SUZY DIRECTOR

Using a crosstab and the only option for returning a text value are first
and last, but nothing to return all values. Any thoughts out there?
Thanks for the assistance.
Apologies if this is posted twice, did not appear that my first post was
saved.
Click to expand...
Click to expand...
 
Back
Top