Cross tab query with more than one data column

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
 
S

Smartin

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

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
 
G

Guest

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top