Having troubles with calculated fields (1 Viewer)

[BlueJacket]

I have two sub reports in a main report. One sub report is for different sources of revenue and the other sub report is different sources of expenses for any given investment fund.

So for example:
Republic Holdings, LLC
Revenue Sub Report
Rent Income: $ 4,800.00
Sold Income: $981,006.98
Redeemed Income: $ 97,010.63

Expense Sub Report
Maintenance and Repairs: $44,496.00
Taxes: $37,085.84
Attorney Fees: $87,173.00
Attorney Costs: $45,299.25

Then in each group footer, I have the Revenue and Expense totals. While Republic Holdings may only have those four type of expenses associated with it, others may have more or less.

My question is: Is there a way I can have calculated fields in my main report based off the numbers in the subreport? Specifically, I want to be able to divide each of those expenses to a total revenue field.

I've been trying to see if I can make that work in query form, but that has also been a headbanger.

Thanks in advance.

 

[plog]

You haven't provided enough infromation about your tables, but in short, yes you can. First you would make a query to determine your total revenue by investment fund. The SQL would look like so:

SELECT InvestmentFundID, SUM(RevenueField) AS TotalRevenue
FROM RevenueDataSource
GROUP BY InvestmentFundID

Save that and let's call it 'sub1'. Then, you would bring sub1 into your Expense query, link it via InvestmentFundID and you would have the TotalRevenue field available to every record where you could then do division to get its percentage of that value.