Tranform Pivot Query?

[tsluu]

I hv 2 tables with structure as below:

[Empl]
ID Name
1 Mr A
2 Mr B
3 Mr C
4 Mr D
5 Mr E

[Empl_Tran]
TrID Empl_ID Date Code
1 1 10/11/2009 SL
2 1 10/11/2009 TR
3 1 11/11/2009 W
4 1 12/11/2009 W
5 1 12/11/2009 TR
6 2 10/11/2009 SL
7 2 10/11/2009 TR
8 2 11/11/2009 W
9 2 12/11/2009 W
10 2 12/11/2009 TR
11 3 10/11/2009 SL
12 3 10/11/2009 TR
13 3 11/11/2009 W
14 3 12/11/2009 W
15 3 12/11/2009 TR


Can I use the Transform..pivot query to get the following results?

Col1 Col2 col3 col4

10/11/2009 11/11/2009 12/11/2009
Mr A SL, TR W W,TR
Mr B SL, TR W W,TR
Mr C SL, TR W W,TR

 

[vanderghast]

Concatenate the codes by empl_id and date, BEFORE making the crosstab
query? It will be then a simple crosstab query (over a query, rather than
directly over a table).


Vanderghast, Access MVP

 

[tsluu]

Hi vanderghast,

How do you actually concatenate the codes? Do you mind showing me some codes?

Concatenate the codes by empl_id and date, BEFORE making the crosstab
query? It will be then a simple crosstab query (over a query, rather than
directly over a table).


Vanderghast, Access MVP

I hv 2 tables with structure as below:

[Empl]
ID Name
1 Mr A
2 Mr B
3 Mr C
4 Mr D
5 Mr E

[Empl_Tran]
TrID Empl_ID Date Code
1 1 10/11/2009 SL
2 1 10/11/2009 TR
3 1 11/11/2009 W
4 1 12/11/2009 W
5 1 12/11/2009 TR
6 2 10/11/2009 SL
7 2 10/11/2009 TR
8 2 11/11/2009 W
9 2 12/11/2009 W
10 2 12/11/2009 TR
11 3 10/11/2009 SL
12 3 10/11/2009 TR
13 3 11/11/2009 W
14 3 12/11/2009 W
15 3 12/11/2009 TR


Can I use the Transform..pivot query to get the following results?

Col1 Col2 col3 col4

10/11/2009 11/11/2009 12/11/2009
Mr A SL, TR W W,TR
Mr B SL, TR W W,TR
Mr C SL, TR W W,TR

 

[John Spencer]

Here are links to three examples.

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

Hi vanderghast,

How do you actually concatenate the codes? Do you mind showing me some codes?

Concatenate the codes by empl_id and date, BEFORE making the crosstab
query? It will be then a simple crosstab query (over a query, rather than
directly over a table).


Vanderghast, Access MVP

I hv 2 tables with structure as below:

[Empl]
ID Name
1 Mr A
2 Mr B
3 Mr C
4 Mr D
5 Mr E

[Empl_Tran]
TrID Empl_ID Date Code
1 1 10/11/2009 SL
2 1 10/11/2009 TR
3 1 11/11/2009 W
4 1 12/11/2009 W
5 1 12/11/2009 TR
6 2 10/11/2009 SL
7 2 10/11/2009 TR
8 2 11/11/2009 W
9 2 12/11/2009 W
10 2 12/11/2009 TR
11 3 10/11/2009 SL
12 3 10/11/2009 TR
13 3 11/11/2009 W
14 3 12/11/2009 W
15 3 12/11/2009 TR


Can I use the Transform..pivot query to get the following results?

Col1 Col2 col3 col4

10/11/2009 11/11/2009 12/11/2009
Mr A SL, TR W W,TR
Mr B SL, TR W W,TR
Mr C SL, TR W W,TR