How to Divide a field's value by its sum?

prabha_friend

Prabhakaran Karuppaih
Local time
, 04:23
Joined
Mar 22, 2009
Messages
878
I have an alias named [Net Income]

http://www.access-programmers.co.uk/forums/showthread.php?t=277736

I want to divide it's value by the sum of its values through another alias [%]. How to do it?

%:=[Net Income]/Sum([Net Income]) Not working :(

getting an error saying:

"You tried to execute a query that does not include the specified expression 'SR_NO' as part of an aggregate function."
 
I think it might be easier to do it on the form or report.

alternatively, have a totals query that generates the total, and include that in your query, WITHOUT trying to join it to the main query.

effectively you will get a cross-product join, but as you only have one item in the total query, you won't notice it.
 
You Sum the expression not the alias.

For example:
Code:
%:=[Net Income]/Sum([Income] * [Tax])

%:=([Income] * [Tax])/Sum([Income] * [Tax])
Where [Income] * [Tax] is equal to the [Net Income] calculation.
 
Hi Inet,

%: [Net Income]/Sum(([Cancellation Fees]+[Issuance Fees]+[Cancellation Fees GDN]+[Issuance Fees GDN]+[Other Revenues*]+[Dividend Fees]+[Tax Reclaim Service]+[Depository Fees]+[OOP Recoveries]+[Broker Reclaims]+[MAX Revenues*]+[FX Fees]+[Pink Sheets]+[NII Revenues*])+([Contrib# Expenses*]+[OOP Expenses*]+[Transfer Agency*]+[Other Expenses*]))

giving the same error:
"You tried to execute a query that does not include the specified expression 'SR_NO' as part of an aggregate function."
 
I'm guessing that you think you are not doing an aggregate query and SR_NO is another field in the query.
Once you include a function such as Sum you have a Totals/aggregate query and all fields must comply.
I don't think you need the sum, else select a totals query and the appropriate selections.

Brian
 

Users who are viewing this thread

Back
Top Bottom