how do you have 2 columns totaled in a cross tab query

[kscheu via AccessMonster.com]

I need to produce a report by division, product, state and salesman that
totals price and quantity for years 2003 thru 2005.
I thought of a cross tab query, but I can only sum on one field and can only
hav max of 3 row headings. How can I accomplish this.

I created a query that selects all the fields I need and a year field. I
then created a cross tab query, but I can only sum the price. I want to sum
the quantity also so that the output looks like this:

2003
2004 2005
QTY Price
QTY Price QTY Price
Division: XXXXX
Product State Salesman
Item1 NJ SM1
Item1 NJ SM2
Item2 CA SM5
Item3 NY SM1

 

[Duane Hookom]

You can have many more than 3 row headings. The "3" limit might be a limit
within the wizard.

You can create a multi-valued crosstab query in a couple different ways. One
of them is documented at http://www.tek-tips.com/faqs.cfm?fid=4524.

 

[Gary Walter]

I need to produce a report by division, product, state and salesman that
totals price and quantity for years 2003 thru 2005.
I thought of a cross tab query, but I can only sum on one field and can
only
hav max of 3 row headings. How can I accomplish this.

I created a query that selects all the fields I need and a year field. I
then created a cross tab query, but I can only sum the price. I want to
sum
the quantity also so that the output looks like this:

2003
2004 2005
QTY Price
QTY Price QTY Price
Division: XXXXX
Product State Salesman
Item1 NJ SM1
Item1 NJ SM2
Item2 CA SM5
Item3 NY SM1

What meaningful result do you hope to get
by summing the price?

I bet you meant summing QTY & (QTY*Price)?

 

[kscheu via AccessMonster.com]

Thanks Duane,

I did not know that. Now I am able to produce what I need after reading the
link that you provided.

Thanks!

You can have many more than 3 row headings. The "3" limit might be a limit
within the wizard.

You can create a multi-valued crosstab query in a couple different ways. One
of them is documented at http://www.tek-tips.com/faqs.cfm?fid=4524.

I need to produce a report by division, product, state and salesman that
totals price and quantity for years 2003 thru 2005.

[quoted text clipped - 17 lines]

Item2 CA SM5
Item3 NY SM1

 

[kscheu via AccessMonster.com]

That is correct. My price field is actually extended price.

I need to produce a report by division, product, state and salesman that
totals price and quantity for years 2003 thru 2005.

[quoted text clipped - 17 lines]

Item2 CA SM5
Item3 NY SM1

What meaningful result do you hope to get
by summing the price?

I bet you meant summing QTY & (QTY*Price)?