How to Calculate a Percentage of a total (1 Viewer)

Perissos

Registered User.
Local time
Today, 19:42
Joined
Jun 28, 2010
Messages
61
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

AWF VIP
Local time
Today, 23:42
Joined
Jan 22, 2010
Messages
26,374
Let's see the SQL statement of the crosstab query.
 

Perissos

Registered User.
Local time
Today, 19:42
Joined
Jun 28, 2010
Messages
61
TRANSFORM Sum(tblPayments.fldAmtPd) AS SumOffldAmtPd
SELECT tblPayments.fldType
FROM tblPayments
GROUP BY tblPayments.fldType
PIVOT Format([fldPostDate],"mmm-yy");
 

vbaInet

AWF VIP
Local time
Today, 23:42
Joined
Jan 22, 2010
Messages
26,374
Ok. Are you displaying the crosstab in a subreport or is it's linked up to textboxes on the report?
 

vbaInet

AWF VIP
Local time
Today, 23:42
Joined
Jan 22, 2010
Messages
26,374
So what will happen is the report will fail next month. Do you realise that?
 

Users who are viewing this thread

Top Bottom