Prev: Remaining Balance Query
Next: Monthly quantities to Weekly
From: Blondee on 15 Apr 2010 10:28
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.
From: Jerry Whittle on 15 Apr 2010 10:58
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Blondee" wrote:

> 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.
From: Duane Hookom on 15 Apr 2010 12:00
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" wrote:

> 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.
From: Blondee on 15 Apr 2010 17:11
Thanks for the assistance -- I will give it a try.

"Duane Hookom" wrote:

> 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" wrote:
>
> > 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.
 | 
Pages: 1
Prev: Remaining Balance Query
Next: Monthly quantities to Weekly