Error: Cannot use the crosstab of a non-fixed column as a subquery

C

ChuckW

Hello,

I have a cross tab query below which creates a table that has years as row
headers and months as column or field headers. There is also a field called
ListId which is the customer number and type which is the category of client
that are row headers as well. I have my sql below for the cross tab query.
------------------------------------------------------------------------------------
TRANSFORM Sum(MonthlySalesElisa.Sales) AS SumOfSales
SELECT MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year, Sum(MonthlySalesElisa.Sales) AS [Total Of Sales]
FROM MonthlySalesElisa
GROUP BY MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year
PIVOT MonthlySalesElisa.Month;
--------------------------------------------------------------------------------------
I have a separate table called Budget20008 that lists clientID and the 12
months of the year as column or field headers. The records in this table
have all of our clients and the projected sales for each month.

What I want to do is to somehow combine the results of this cross tab with
the data from the Budget2008 table. The combined data would look like the
following:

ListID Year Type Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
1101 2007 C $100 $200 $150 $200 $300 $200 $100 $150 $300 $275 $200 $100
1101 2008 C $150
1101 Budget C $200

The first two records would come from the cross tab query while the third
record would come from the Budget2008 table. I wanted to run some type of
union query that could capture both. I tried running one but received an
error message stating that states "Cannot use the crosstab of a non-fixed
cloumn as a subquery.

Can someone help?
 
K

KARL DEWEY

Try changing the crosstab like this ---
PIVOT MonthlySalesElisa.Month IN(“Janâ€, “Febâ€, “Marâ€, “Aprâ€, “Mayâ€, “Junâ€,
“Julâ€, “Augâ€, “Sepâ€, “Octâ€, “Novâ€, “Decâ€);
 
C

ChuckW

Karl,

I modified the sql but I get a "Synatax error in transform statement". I am
wondering if I need to create make table based on the cross tab and then
write the union query based on this table.


--
Chuck W


KARL DEWEY said:
Try changing the crosstab like this ---
PIVOT MonthlySalesElisa.Month IN(“Janâ€, “Febâ€, “Marâ€, “Aprâ€, “Mayâ€, “Junâ€,
“Julâ€, “Augâ€, “Sepâ€, “Octâ€, “Novâ€, “Decâ€);

--
KARL DEWEY
Build a little - Test a little


ChuckW said:
Hello,

I have a cross tab query below which creates a table that has years as row
headers and months as column or field headers. There is also a field called
ListId which is the customer number and type which is the category of client
that are row headers as well. I have my sql below for the cross tab query.
------------------------------------------------------------------------------------
TRANSFORM Sum(MonthlySalesElisa.Sales) AS SumOfSales
SELECT MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year, Sum(MonthlySalesElisa.Sales) AS [Total Of Sales]
FROM MonthlySalesElisa
GROUP BY MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year
PIVOT MonthlySalesElisa.Month;
--------------------------------------------------------------------------------------
I have a separate table called Budget20008 that lists clientID and the 12
months of the year as column or field headers. The records in this table
have all of our clients and the projected sales for each month.

What I want to do is to somehow combine the results of this cross tab with
the data from the Budget2008 table. The combined data would look like the
following:

ListID Year Type Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
1101 2007 C $100 $200 $150 $200 $300 $200 $100 $150 $300 $275 $200 $100
1101 2008 C $150
1101 Budget C $200

The first two records would come from the cross tab query while the third
record would come from the Budget2008 table. I wanted to run some type of
union query that could capture both. I tried running one but received an
error message stating that states "Cannot use the crosstab of a non-fixed
cloumn as a subquery.

Can someone help?
Click to expand...
Click to expand...
 
K

KARL DEWEY

That should do it.
--
KARL DEWEY
Build a little - Test a little


ChuckW said:
Karl,

I modified the sql but I get a "Synatax error in transform statement". I am
wondering if I need to create make table based on the cross tab and then
write the union query based on this table.


--
Chuck W


KARL DEWEY said:
Try changing the crosstab like this ---
PIVOT MonthlySalesElisa.Month IN(“Janâ€, “Febâ€, “Marâ€, “Aprâ€, “Mayâ€, “Junâ€,
“Julâ€, “Augâ€, “Sepâ€, “Octâ€, “Novâ€, “Decâ€);

--
KARL DEWEY
Build a little - Test a little


ChuckW said:
Hello,

I have a cross tab query below which creates a table that has years as row
headers and months as column or field headers. There is also a field called
ListId which is the customer number and type which is the category of client
that are row headers as well. I have my sql below for the cross tab query.
------------------------------------------------------------------------------------
TRANSFORM Sum(MonthlySalesElisa.Sales) AS SumOfSales
SELECT MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year, Sum(MonthlySalesElisa.Sales) AS [Total Of Sales]
FROM MonthlySalesElisa
GROUP BY MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year
PIVOT MonthlySalesElisa.Month;
--------------------------------------------------------------------------------------
I have a separate table called Budget20008 that lists clientID and the 12
months of the year as column or field headers. The records in this table
have all of our clients and the projected sales for each month.

What I want to do is to somehow combine the results of this cross tab with
the data from the Budget2008 table. The combined data would look like the
following:

ListID Year Type Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
1101 2007 C $100 $200 $150 $200 $300 $200 $100 $150 $300 $275 $200 $100
1101 2008 C $150
1101 Budget C $200

The first two records would come from the cross tab query while the third
record would come from the Budget2008 table. I wanted to run some type of
union query that could capture both. I tried running one but received an
error message stating that states "Cannot use the crosstab of a non-fixed
cloumn as a subquery.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
G

George Nicholson

You shouldn't need to a make-table, but you do have the right idea: you need
an interim step between the crosstab and the Union.

You can use either a make-table or another select query ("SELECT * FROM
myCrossTab"). Then have your UNION reference either the table or the query.
You just can't have the UNION reference the crosstab directly. UNIONs need
straightforward SELECT queries and the TRANSFORM/PIVOT in a crosstab seems
to disqualify it.

--
HTH,
George


ChuckW said:
Karl,

I modified the sql but I get a "Synatax error in transform statement". I
am
wondering if I need to create make table based on the cross tab and then
write the union query based on this table.


--
Chuck W


KARL DEWEY said:
Try changing the crosstab like this ---
PIVOT MonthlySalesElisa.Month IN("Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec");

--
KARL DEWEY
Build a little - Test a little


ChuckW said:
Hello,

I have a cross tab query below which creates a table that has years as
row
headers and months as column or field headers. There is also a field
called
ListId which is the customer number and type which is the category of
client
that are row headers as well. I have my sql below for the cross tab
query.
------------------------------------------------------------------------------------
TRANSFORM Sum(MonthlySalesElisa.Sales) AS SumOfSales
SELECT MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year, Sum(MonthlySalesElisa.Sales) AS [Total Of
Sales]
FROM MonthlySalesElisa
GROUP BY MonthlySalesElisa.ListID, MonthlySalesElisa.Type,
MonthlySalesElisa.Year
PIVOT MonthlySalesElisa.Month;
--------------------------------------------------------------------------------------
I have a separate table called Budget20008 that lists clientID and the
12
months of the year as column or field headers. The records in this
table
have all of our clients and the projected sales for each month.

What I want to do is to somehow combine the results of this cross tab
with
the data from the Budget2008 table. The combined data would look like
the
following:

ListID Year Type Jan Feb Mar Apr May Jun Jul Aug Sep
Oct
Nov Dec
1101 2007 C $100 $200 $150 $200 $300 $200 $100 $150 $300 $275
$200 $100
1101 2008 C $150
1101 Budget C $200

The first two records would come from the cross tab query while the
third
record would come from the Budget2008 table. I wanted to run some type
of
union query that could capture both. I tried running one but received
an
error message stating that states "Cannot use the crosstab of a
non-fixed
cloumn as a subquery.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

need help making a query (crosstab instead of Pivot tables?) 2
cannot use the crosstab of a non-fixed column as a subquery 3
crosstab query column headers do not match data in cells 1
Summing by year in a cross tab query 2
Help needed with this running totals crosstab query.... 3
Renaming Column Headers with IIf as a Result of a Crosstab Query 3
Use crosstab column heading in a calculation 31
Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks) 7

Top