How to Calculate a Percentage of a total

[Perissos]

I have a report based on a crosstab query that I thought would be easy to do. It basically shows the information in the query and then puts a grand total at the bottom. Piece of cake until it asks for a percentage to be calculated off a portion of the detail.

Example of Data on Report

Month1 Month2 Month3
Item1 10.00 0.00 30.00
Item2 10.00 10.00 10.00

Total 20.00 10.00 40.00
Pct 50% 100% 25% (trouble line)

My problem occurs when I try to add the percentage calculation in. For each column I want to take, for example Item 2/Total. I tried grouping with the % of total but it gave me both items with percents. I currently have the Total as a report Footer. This has got to be something simple that I just dont remember how to do. I am currently trying to use the dsum but am getting errors that the field name, query name, report name..... is not valid

 

[vbaInet]

Let's see the SQL statement of the crosstab query.

 

[Perissos]

TRANSFORM Sum(tblPayments.fldAmtPd) AS SumOffldAmtPd
SELECT tblPayments.fldType
FROM tblPayments
GROUP BY tblPayments.fldType
PIVOT Format([fldPostDate],"mmm-yy");

 

[vbaInet]

Ok. Are you displaying the crosstab in a subreport or is it's linked up to textboxes on the report?

 

[Perissos]

text boxes on the report

 

[vbaInet]

So what will happen is the report will fail next month. Do you realise that?