Totals Column of Weekly System Group totals 2

[netrusher]

Below is the code that I have for a crosstab query. This shows me columns by week with totals for system groups in the columns. What I would like to be able to do is to get a Grand totals column of each system group that would show the totals of each week added together. Does anyone know how to accomplish this?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum([Trends-Mechanical-1-3TON-WEEK].[Mechanical Totals]) AS [SumOfMechanical Totals1]
SELECT [Trends-Mechanical-1-3TON-WEEK].SystemGroup
FROM [Trends-Mechanical-1-3TON-WEEK]
GROUP BY [Trends-Mechanical-1-3TON-WEEK].SystemGroup
PIVOT [Trends-Mechanical-1-3TON-WEEK].YearMonthWeek;

 

[lespaul]

If this query is the source for a report, you can perform totals in the report rather than the query.

Leslie

 

[netrusher]

Not for a report.

 

[PHV]

Perhaps this ?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum([Trends-Mechanical-1-3TON-WEEK].[Mechanical Totals]) AS [SumOfMechanical Totals1]
SELECT [Trends-Mechanical-1-3TON-WEEK].SystemGroup
[!], Sum([Trends-Mechanical-1-3TON-WEEK].[Mechanical Totals]) AS [Total][/!]
FROM [Trends-Mechanical-1-3TON-WEEK]
GROUP BY [Trends-Mechanical-1-3TON-WEEK].SystemGroup
PIVOT [Trends-Mechanical-1-3TON-WEEK].YearMonthWeek;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

WOW!
Exactly what I was search for.

Thanks a lot!

 

[netrusher]

Is there any way to sort by the Total Field??

 

[lespaul]

maybe:

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum([Trends-Mechanical-1-3TON-WEEK].[Mechanical Totals]) AS [SumOfMechanical Totals1]
SELECT [Trends-Mechanical-1-3TON-WEEK].SystemGroup
, Sum([Trends-Mechanical-1-3TON-WEEK].[Mechanical Totals]) AS [Total]
FROM [Trends-Mechanical-1-3TON-WEEK]
GROUP BY [Trends-Mechanical-1-3TON-WEEK].SystemGroup
[b]ORDER BY 2[/b]
PIVOT [Trends-Mechanical-1-3TON-WEEK].YearMonthWeek;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

Miss Leslie,

Below is the code I now have for the query. It has changed some from earlier. I added the Order by 2 part that you added above and I get the totals but they are not sorted.
Any suggestions?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY 2
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

 

[lespaul]

well the original query you posted had two fields in the result set: SystemGroup and Total, the ORDER BY 2 means order by the second field in the result set. The query you have just posted has the total as the THIRD field. You would have to do ORDER BY 3.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

This is my code now. Still no change in order.

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY 3
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

 

[PHV]

I'm afraid that an ORDER BY clause is simply ignored in a crosstab query ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Thanks Leslie and PH

 

[lespaul]

that's helpful to know!