How do you create a Matrix (Crosstab) on Same Field?

[dmarek]

Hello,

I have a table that has lets say:


Store Program Business Sales
9999 1 A 100
9999 B B 150
1000 2 A 100
1000 B B 150
9000 1 A 100
9000 A B 150
2000 2 A 100
2000 C B 150

I want to crosstab this to put it in a matrix that looks like this:


______A_______B______C

1____250_____250_____0

2 ____0______250_____250

Where underscores are blanks (couldn't get it to display properly).

How do I split out the Program by Business Types to the Row/Column Headers and ensure the values calculate properly off the combination?

 

[alansidman]

Does this help you?

http://www.datapigtechnologies.com/flashfiles/crosstab.html

Alan

 

[dmarek]

So what I'm taking from that video is you are suggesting I don't use a cross tab and do it manually?

Which could work...

 

[xenou]

A crosstab query should work:

Program in Row (group by), Business in Column (group by), Sales in Value (sum)

ξ

 

[alansidman]

The video suggests you make the cross tab manually versus using the wizard which has some limitations. But as Xenou has said, a cross tab query will do it. Pick your poison. Manual or Wizard.

Alan