Percent of Total Cross Tab Query

[JMichaelM]

Have a cross tab with a total of each row and want to add percentage of total to each row similar to a pivot. It seems like it shouldnt be that difficult. Any guidance will save me time. Thanks.

 

[arnelgp]

create a Total Query. source table of Total query is same as your XTab query:

SELECT Table1.PKField, Sum(Table1.QTY) AS SumOfQTY
FROM Table1
GROUP BY Table1.PKField;


save the query

'--------

now create a nother query that will Join your XTab and the Total Query.
add another column to this query that will calculate the percentage:

SELECT XTAB.*, Query2.SumOfQTY/DSum("QTY","TABLE1") AS Expr1
FROM XTAB LEFT JOIN Query2 ON XTAB.PKField = Query2.PKField;

 

[JMichaelM]

Got it thanks. Seems like a report was easier.