Crosstab - with one column heading and more than one value field

[Anj]

Hi,

I understand that the basic definition of crosstab limits me to a single
value field, but is there a way I can work around this issue? I don't want to
write seperate crosstab queries and merge them on to a single table (I am
designing a form and don't want to write a lot of queries to get to one
table).

ex I have a table:

Sales_rep Postingperiod Revenue Cost Margins
A 2005 1 11 21
B 2005 2 12 22
C 2005 3 13 23
A 2006 4 14 24
B 2006 5 15 25
C 2006 6 16 26
A 2007 7 17 27
B 2007 8 18 28
C 2007 9 19 29

I want the final table as: (Crosstab on previous table with postingperiod as
column heading)

Sales_Rep 2005 2006 2007
A 1 4 7
B 2 5 8
C 3 6 9
A 11 14 17
B 12 15 18
C 13 16 19
A 21 24 27
B 22 25 28
C 23 26 29

Thanks,
Anj

 

[Allen Browne]

Not the simpest thing in the world, but it is doable. See:
Crosstab query techniques: Multiple sets of values
at:
http://allenbrowne.com/ser-67.html#MultipleValues

 

[John W. Vinson/MVP]

(I am
designing a form and don't want to write a lot of queries to get to one
table).

Do note that a crosstab query - single or multivalued - will NOT be
updateable. If you're trying to have this crosstab query to let users
enter or edit data you'll be disappointed!

 

[Anj]

Thank you. It worked well.

Not the simpest thing in the world, but it is doable. See:
Crosstab query techniques: Multiple sets of values
at:
http://allenbrowne.com/ser-67.html#MultipleValues

 

[Anj]

I have a table that I have created from the crosstab query. I am using the
same in my form to avoid that issue. Thank you.