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

  • Thread starter Thread starter Michelle Ives
  • Start date Start date
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;
 
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;
 
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...
 
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...
 
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...
 
Back
Top