Cross tab query with more than one data column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know how to add a column to a crosstab query since the wizard only
allows one column. The columns are monthly data, but I need 2 columns for
each month,one for cost and one for price.

TRANSFORM Sum([3Q05CrPvt].EXT_PRICE) AS SumOfEXT_PRICE
SELECT [3Q05CrPvt].LOC, [3Q05CrPvt].TRANS_CODE
FROM 3Q05CrPvt
GROUP BY [3Q05CrPvt].LOC, [3Q05CrPvt].TRANS_CODE
PIVOT [3Q05CrPvt].Month;

I need this code to generate a column for EXT_PRICE and EXT_COST
 
JoeA2006 said:
I need to know how to add a column to a crosstab query since the wizard only
allows one column. The columns are monthly data, but I need 2 columns for
each month,one for cost and one for price.

TRANSFORM Sum([3Q05CrPvt].EXT_PRICE) AS SumOfEXT_PRICE
SELECT [3Q05CrPvt].LOC, [3Q05CrPvt].TRANS_CODE
FROM 3Q05CrPvt
GROUP BY [3Q05CrPvt].LOC, [3Q05CrPvt].TRANS_CODE
PIVOT [3Q05CrPvt].Month;

I need this code to generate a column for EXT_PRICE and EXT_COST
Click to expand...

There is a sample A2000 download here (look for "Database of Access 2000
sample queries"):
http://office.microsoft.com/en-us/access/HA011201381033.aspx

And an article here:
http://support.microsoft.com/kb/304458/

Be warned.. you don't get the same flexibility Excel provides in a pivot
table, and it's pretty cumbersome. You may find it easier to use Excel
for this. Just point the pivot table wizard at your database and off you go.

HTH
 
UNTESTED --
TRANSFORM Sum([3Q05CrPvt].EXT_PRICE) & " " & Sum([3Q05CrPvt].EXT_COST)AS
EXPR1
SELECT [3Q05CrPvt].LOC, [3Q05CrPvt].TRANS_CODE
FROM 3Q05CrPvt
GROUP BY [3Q05CrPvt].LOC, [3Q05CrPvt].TRANS_CODE
PIVOT [3Q05CrPvt].Month;
 
Back
Top