I need to include a field in a crosstab query just to sort

M

Michelle Ives

I have the following four CBTs (Customer Business Teams) "AT&T; MNO; MSO; NAM
SbR", with costs that are tracked on a weekly basis for budgeting purposes. I
figured out how to use a cartesian query to make sure that I get a column
heading for all 26 weeks of the first half 2009, even if no costs exist for
some weeks, and my final crosstab query is almost perfect, except that I need
the sort order of the CBT names to = "AT&T; NAM SbR; MNO; MSO". I have a
number field in the CBT table called [CBTNo] that I can use to force this
sort in all my other reports, but when I try to add it to my crosstab query I
get too many rows if I use "Group By". I tried "Where" with "Not Shown", but
if I include a sort order, I get the error message "You tried to execute a
query that does not include ... as part of an aggregate function". If I can't
sort on the field, it's useless. Am I missing something obvious? I tried
sorting on the field in the cartesian query used for the crosstab, but the
crosstab ignores it.

TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM qcartFinalAssignWkCbt
GROUP BY qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
PIVOT qcartFinalAssignWkCbt.YWk;
 
K

KARL DEWEY

Try this, replacing [CBT table] with your actual table name --
TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM [CBT table] LEFT JOIN qcartFinalAssignWkCbt ON [CBT table].[CbtName] =
qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY [CBT table].[CBTNo]
PIVOT qcartFinalAssignWkCbt.YWk;
 
K

KARL DEWEY

Or maybe --
Try this, replacing [CBT table] with your actual table name --
TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM [CBT table] LEFT JOIN qcartFinalAssignWkCbt ON [CBT table].[CbtName] =
qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY [CBT table].[CBTNo], qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
PIVOT qcartFinalAssignWkCbt.YWk;


KARL DEWEY said:
Try this, replacing [CBT table] with your actual table name --
TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM [CBT table] LEFT JOIN qcartFinalAssignWkCbt ON [CBT table].[CbtName] =
qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY [CBT table].[CBTNo]
PIVOT qcartFinalAssignWkCbt.YWk;


Michelle Ives said:
I have the following four CBTs (Customer Business Teams) "AT&T; MNO; MSO; NAM
SbR", with costs that are tracked on a weekly basis for budgeting purposes. I
figured out how to use a cartesian query to make sure that I get a column
heading for all 26 weeks of the first half 2009, even if no costs exist for
some weeks, and my final crosstab query is almost perfect, except that I need
the sort order of the CBT names to = "AT&T; NAM SbR; MNO; MSO". I have a
number field in the CBT table called [CBTNo] that I can use to force this
sort in all my other reports, but when I try to add it to my crosstab query I
get too many rows if I use "Group By". I tried "Where" with "Not Shown", but
if I include a sort order, I get the error message "You tried to execute a
query that does not include ... as part of an aggregate function". If I can't
sort on the field, it's useless. Am I missing something obvious? I tried
sorting on the field in the cartesian query used for the crosstab, but the
crosstab ignores it.

TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM qcartFinalAssignWkCbt
GROUP BY qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
PIVOT qcartFinalAssignWkCbt.YWk;
Click to expand...
Click to expand...
 
M

Michelle Ives

Hi - Tried this:

TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM CBT LEFT JOIN qcartFinalAssignWkCbt ON
CBT.CbtName=qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY CBT.CBTNo
PIVOT qcartFinalAssignWkCbt.YWk;

"You tried to execute a query that does not include the specified expression
'CBTName' as part of an aggregate function.'

Thanks for your quick response!

KARL DEWEY said:
Try this, replacing [CBT table] with your actual table name --
TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM [CBT table] LEFT JOIN qcartFinalAssignWkCbt ON [CBT table].[CbtName] =
qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY [CBT table].[CBTNo]
PIVOT qcartFinalAssignWkCbt.YWk;


Michelle Ives said:
I have the following four CBTs (Customer Business Teams) "AT&T; MNO; MSO; NAM
SbR", with costs that are tracked on a weekly basis for budgeting purposes. I
figured out how to use a cartesian query to make sure that I get a column
heading for all 26 weeks of the first half 2009, even if no costs exist for
some weeks, and my final crosstab query is almost perfect, except that I need
the sort order of the CBT names to = "AT&T; NAM SbR; MNO; MSO". I have a
number field in the CBT table called [CBTNo] that I can use to force this
sort in all my other reports, but when I try to add it to my crosstab query I
get too many rows if I use "Group By". I tried "Where" with "Not Shown", but
if I include a sort order, I get the error message "You tried to execute a
query that does not include ... as part of an aggregate function". If I can't
sort on the field, it's useless. Am I missing something obvious? I tried
sorting on the field in the cartesian query used for the crosstab, but the
crosstab ignores it.

TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM qcartFinalAssignWkCbt
GROUP BY qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
PIVOT qcartFinalAssignWkCbt.YWk;
Click to expand...
Click to expand...
 
M

Michelle Ives

That did the trick! Thanks so much!

KARL DEWEY said:
Or maybe --
Try this, replacing [CBT table] with your actual table name --
TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM [CBT table] LEFT JOIN qcartFinalAssignWkCbt ON [CBT table].[CbtName] =
qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY [CBT table].[CBTNo], qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
PIVOT qcartFinalAssignWkCbt.YWk;


KARL DEWEY said:
Try this, replacing [CBT table] with your actual table name --
TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM [CBT table] LEFT JOIN qcartFinalAssignWkCbt ON [CBT table].[CbtName] =
qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
GROUP BY [CBT table].[CBTNo]
PIVOT qcartFinalAssignWkCbt.YWk;


Michelle Ives said:
I have the following four CBTs (Customer Business Teams) "AT&T; MNO; MSO; NAM
SbR", with costs that are tracked on a weekly basis for budgeting purposes. I
figured out how to use a cartesian query to make sure that I get a column
heading for all 26 weeks of the first half 2009, even if no costs exist for
some weeks, and my final crosstab query is almost perfect, except that I need
the sort order of the CBT names to = "AT&T; NAM SbR; MNO; MSO". I have a
number field in the CBT table called [CBTNo] that I can use to force this
sort in all my other reports, but when I try to add it to my crosstab query I
get too many rows if I use "Group By". I tried "Where" with "Not Shown", but
if I include a sort order, I get the error message "You tried to execute a
query that does not include ... as part of an aggregate function". If I can't
sort on the field, it's useless. Am I missing something obvious? I tried
sorting on the field in the cartesian query used for the crosstab, but the
crosstab ignores it.

TRANSFORM Sum(qcartFinalAssignWkCbt.EndCost) AS SumOfEndCost
SELECT qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName,
Sum(qcartFinalAssignWkCbt.EndCost) AS [Total Of EndCost]
FROM qcartFinalAssignWkCbt
GROUP BY qcartFinalAssignWkCbt.qcartAssignWkCbt.CbtName
PIVOT qcartFinalAssignWkCbt.YWk;
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

Include empty values in crosstab query 5
insert a crosstab in to temp table query 2
Totalling Rows and Columns in a Crosstab Query 1
How to reorder columns in a crosstab query 4
Calculated Field in Crosstab query 1
How to sort columns in a Crosstab Query 2
Crosstab query is no longer crosstab when I include field (in repo 1
How do I include zero-sale months in a Crosstab query? 3

Top