Tranform Pivot Query?

  • Thread starter Thread starter tsluu
  • Start date Start date
T

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
 
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
 
Hi vanderghast,

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

vanderghast said:
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 said:
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
Click to expand...
Click to expand...
 
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?

vanderghast said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top