CrossTab Group Totals

  • Thread starter Thread starter sneeze
  • Start date Start date
S

sneeze

i have created a crosstab query that works great but I have a problem in that
I need a grand total of the field called Total on the last line that totals
the Total column. Any suggestions
 
Make your crosstab from a union query with your table and a totals query that
looks like this --
qryTable --
SELECT YourTable.Product, YourTable.ProdDate, YourTable.Score
FROM YourTable
UNION ALL SELECT "Grand_Total" AS Product, Null AS ProdDate,
Sum(YourTable.Score) AS Total_Score
FROM YourTable
GROUP BY "Grand_Total", Null;

TRANSFORM Sum(qryYourTable.[Score]) AS SumOfScore
SELECT qryYourTable.[Product], Sum(qryYourTable.[Score]) AS [Total Of Score]
FROM qryYourTable
GROUP BY qryYourTable.[Product]
PIVOT Format([ProdDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 
Back
Top