How to Divide a field's value by its sum? (1 Viewer)

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 20:31
Joined
Mar 22, 2009
Messages
771
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."
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:01
Joined
Sep 12, 2006
Messages
15,614
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.
 

vbaInet

AWF VIP
Local time
Today, 15:01
Joined
Jan 22, 2010
Messages
26,374
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.
 

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 20:31
Joined
Mar 22, 2009
Messages
771
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."
 

Brianwarnock

Retired
Local time
Today, 15:01
Joined
Jun 2, 2003
Messages
12,701
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

Top Bottom