Pivot Tables: Is this possible?

[ed]

I have data that looks like this:

"HOUSE","MONTH","MILK-QTY","OJ-QTY","SODA-QTY","MILK-CST","OJ-CST","SODA-CST"
house1,jan,A,B,C,1.00,2.00,3.00
house2,jan,A,B,C,4.00,5.00,6.00
house3,jan,A,B,C,7.00,8.00,9.00
house1,feb,D,E,F,1.50,2.50,3.50
.... etc.

The prim key is "HOUSE"+"MONTH" and the "X-QTY" values are always the
same across all houses (they are joined in from a table that is keyed
only by "MONTH").

I want to use a pivot table to arrange it to be like this:

MONTH | DATA | QTY | HOUSE1 | HOUSE2 | HOUSE3
----------------------------------------------
| milk | A | 1.00 | 4.00 | 7.00
jan | oj | B | 2.00 | 5.00 | 8.00
| soda | C | 3.00 | 6.00 | 9.00
----------------------------------------------
| milk | D | 1.50 | 4.50 | 7.50
feb | oj | E | 2.50 | 5.50 | 8.50
| soda | F | 3.50 | 6.50 | 9.50
----------------------------------------------
etc..

It's a cross-tab on "MONTH" and "HOUSE" with the "X-CST" fields as
data values. But I also want the "X-QTY" columns as data fields, but
not associated with HOUSE.

Anyone have any ideas how to get that "QTY" column in there? I've
tried creating a Calculated Item, but I don't think that's going to
get me what I want. What can I do here? Am I stuck?

 

[Glen Bunnell]

Unfortunately, I think you will need to reorganize the
data that you're working with to make this pivot table
work. It needs to be structured as follows (When I
pasted it in, the columns didn't line up) I'm sure this
data could be restructured using some VB. I was then
able to replicate the summary as you outlined.

HOUSE MONTH BEVERAGE QTY CST
HOUSE1 JAN MILK A 1
HOUSE1 JAN OJ B 2
HOUSE1 JAN SODA C 3
HOUSE2 JAN MILK A 4
HOUSE2 JAN OJ B 5
HOUSE2 JAN SODA C 6
HOUSE3 JAN MILK A 7
HOUSE3 JAN OJ B 8
HOUSE3 JAN SODA C 9
HOUSE1 FEB MILK D 1.5
HOUSE1 FEB OJ E 2.5
HOUSE1 FEB SODA F 3.5

Sum of CST HOUSE
MONTH BEVERAGE QTY HOUSE1 HOUSE2 HOUSE3
Grand Total
JAN MILK A 1 4 7 12
OJ B 2 5 8 15
SODA C 3 6 9 18
JAN Total 6 15 24 45
FEB MILK D 1.5 1.5
OJ E 2.5 2.5
SODA F 3.5 3.5
FEB Total 7.5
7.5
Grand Total 13.5 15 24
52.5