Percentage of Total

[Guest]

I think this is so simple, yet alas...

What I am tring to do is create the expression: [Expr1]/sum[Expr1] to give
me percentage of total for individual records. Access gives me a "subquery"
warning. Any workaround? As always, your insight is appreciated!!!

 

[Ken Snell [MVP]]

Can you tell us just a little bit more about what your data are? and the
table structure? and the query SQL statement? and such?

 

[pmkdatabase_at_yahoo_period_ca]

I will be interested to see what anyone can come up with. I have never
been able to do this in one query. I usually just do the % calculation
on a report or make a temporary table and populate it by coding.

I did find a discussion on this at:
http://makeashorterlink.com/?W2BF364CA where using dcount is
suggested. Ugh! Dcount is definitely a last resort, IMO, but perhaps
the discussion will be helpful.

Peter

 

[Andreas]

My solution would be:

1)
Create a query (qryAnalysis01DetailData) that does whatever it does to
create the detail data.
2)
Create a query (qryAnalysis02Totals) that creates the required
GrandTotal or whatever (can be multiple fields but MUST be single record).
3)
Create a query (qryAnalysis)using both the above as data sources with no
link between them. You can now easily do all your calculations.

Regards,
Andreas

I will be interested to see what anyone can come up with. I have never
been able to do this in one query. I usually just do the % calculation
on a report or make a temporary table and populate it by coding.

I did find a discussion on this at:
http://makeashorterlink.com/?W2BF364CA where using dcount is
suggested. Ugh! Dcount is definitely a last resort, IMO, but perhaps
the discussion will be helpful.

Peter

I think this is so simple, yet alas...

What I am tring to do is create the expression: [Expr1]/sum[Expr1] to give
me percentage of total for individual records. Access gives me a "subquery"
warning. Any workaround? As always, your insight is appreciated!!!

 

[Guest]

The query SQL is admittedly quite ugly, I'll spare you the full code.
Basically, data from linked tables are:
Account #
Account Type (several fields)
Account Values (several fields, by asset type)
Account Returns (several fields, by asset type)

My query is mostly Expressions used to calculate returns by asset type. But
I would like to generate Total Weighted Returns (by asset type), and to do
that I need Percentage of Total. As was already suggested, I tried
generating the individual records' percentage of total in a separate query,
and then link the queries, but I still received a "subquery" error message.
Hope this is helpful.

Can you tell us just a little bit more about what your data are? and the
table structure? and the query SQL statement? and such?

--

Ken Snell
<MS ACCESS MVP>

I think this is so simple, yet alas...

What I am tring to do is create the expression: [Expr1]/sum[Expr1] to give
me percentage of total for individual records. Access gives me a
"subquery"
warning. Any workaround? As always, your insight is appreciated!!!

 

[Ken Snell [MVP]]

What is the specific error message that you get? Is your query a select
query? or an action query?

Have you tried using DCount or DSum domain function to get the "denominator"
of the expression?

We may need to see the SQL statement (even if "ugly") to better understand
what you're trying to do.
--

Ken Snell
<MS ACCESS MVP>

The query SQL is admittedly quite ugly, I'll spare you the full code.
Basically, data from linked tables are:
Account #
Account Type (several fields)
Account Values (several fields, by asset type)
Account Returns (several fields, by asset type)

My query is mostly Expressions used to calculate returns by asset type.
But
I would like to generate Total Weighted Returns (by asset type), and to do
that I need Percentage of Total. As was already suggested, I tried
generating the individual records' percentage of total in a separate
query,
and then link the queries, but I still received a "subquery" error
message.
Hope this is helpful.

Can you tell us just a little bit more about what your data are? and the
table structure? and the query SQL statement? and such?

--

Ken Snell
<MS ACCESS MVP>

I think this is so simple, yet alas...

What I am tring to do is create the expression: [Expr1]/sum[Expr1] to
give
me percentage of total for individual records. Access gives me a
"subquery"
warning. Any workaround? As always, your insight is appreciated!!!

 

[Guest]

Got it! Dsum worked. Why would it treat DSum differently from Sum? The
error message I had received before was "Subqueries cannot be used in the
expression ([Expr1]/(Sum([Expr1])). Attached is my code (working!) with the
Dsum expression (Expr6):

SELECT tblSTYLE.STYLE, source.Code, source.Name, performance.ce1,
performance.eq1, performance.fi1, source.Cash, source.Equities, source.Fixed,
Sum([Cash]+[Equities]+[Fixed]) AS Expr1, [Cash]/[Expr1] AS Expr2,
[Equities]/[Expr1] AS Expr3, [Fixed]/[Expr1] AS Expr4,
([ce1]*[Expr2])+([eq1]*[Expr3])+([fi1]*[Expr4]) AS Expr5,
[expr1]/(DSum("[expr1]","qryPerfSmCap")) AS Expr6
FROM (codes INNER JOIN (source INNER JOIN performance ON source.Code =
performance.NUMBER) ON (codes.NUMBER = source.Code) AND (codes.NUMBER =
performance.NUMBER)) INNER JOIN tblSTYLE ON codes.[OPT# CAT- 18] =
tblSTYLE.OPTICODE
GROUP BY tblSTYLE.STYLE, source.Code, source.Name, performance.ce1,
performance.eq1, performance.fi1, source.Cash, source.Equities, source.Fixed
HAVING (((tblSTYLE.STYLE)="smallcap"));

It's a select query.

Many thanks for your time!!!
-Russ

What is the specific error message that you get? Is your query a select
query? or an action query?

Have you tried using DCount or DSum domain function to get the "denominator"
of the expression?

We may need to see the SQL statement (even if "ugly") to better understand
what you're trying to do.
--

Ken Snell
<MS ACCESS MVP>

The query SQL is admittedly quite ugly, I'll spare you the full code.
Basically, data from linked tables are:
Account #
Account Type (several fields)
Account Values (several fields, by asset type)
Account Returns (several fields, by asset type)

My query is mostly Expressions used to calculate returns by asset type.
But
I would like to generate Total Weighted Returns (by asset type), and to do
that I need Percentage of Total. As was already suggested, I tried
generating the individual records' percentage of total in a separate
query,
and then link the queries, but I still received a "subquery" error
message.
Hope this is helpful.

Can you tell us just a little bit more about what your data are? and the
table structure? and the query SQL statement? and such?

--

Ken Snell
<MS ACCESS MVP>

I think this is so simple, yet alas...

What I am tring to do is create the expression: [Expr1]/sum[Expr1] to
give
me percentage of total for individual records. Access gives me a
"subquery"
warning. Any workaround? As always, your insight is appreciated!!!

 

[Ken Snell [MVP]]

DSum is not an aggregate function. Though this will work, just note that it
may cause the query to run a bit slower than if it were not present in the
query.

Also, note that you should not use Name as a field name (see your
source.Name field, for example). It and many other words are reserved words
in ACCESS, and can create serious confusion for ACCESS and Jet. See these
Knowledge Base articles for more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Got it! Dsum worked. Why would it treat DSum differently from Sum? The
error message I had received before was "Subqueries cannot be used in the
expression ([Expr1]/(Sum([Expr1])). Attached is my code (working!) with
the
Dsum expression (Expr6):

SELECT tblSTYLE.STYLE, source.Code, source.Name, performance.ce1,
performance.eq1, performance.fi1, source.Cash, source.Equities,
source.Fixed,
Sum([Cash]+[Equities]+[Fixed]) AS Expr1, [Cash]/[Expr1] AS Expr2,
[Equities]/[Expr1] AS Expr3, [Fixed]/[Expr1] AS Expr4,
([ce1]*[Expr2])+([eq1]*[Expr3])+([fi1]*[Expr4]) AS Expr5,
[expr1]/(DSum("[expr1]","qryPerfSmCap")) AS Expr6
FROM (codes INNER JOIN (source INNER JOIN performance ON source.Code =
performance.NUMBER) ON (codes.NUMBER = source.Code) AND (codes.NUMBER =
performance.NUMBER)) INNER JOIN tblSTYLE ON codes.[OPT# CAT- 18] =
tblSTYLE.OPTICODE
GROUP BY tblSTYLE.STYLE, source.Code, source.Name, performance.ce1,
performance.eq1, performance.fi1, source.Cash, source.Equities,
source.Fixed
HAVING (((tblSTYLE.STYLE)="smallcap"));

It's a select query.

Many thanks for your time!!!
-Russ

What is the specific error message that you get? Is your query a select
query? or an action query?

Have you tried using DCount or DSum domain function to get the
"denominator"
of the expression?

We may need to see the SQL statement (even if "ugly") to better
understand
what you're trying to do.
--

Ken Snell
<MS ACCESS MVP>

The query SQL is admittedly quite ugly, I'll spare you the full code.
Basically, data from linked tables are:
Account #
Account Type (several fields)
Account Values (several fields, by asset type)
Account Returns (several fields, by asset type)

My query is mostly Expressions used to calculate returns by asset type.
But
I would like to generate Total Weighted Returns (by asset type), and to
do
that I need Percentage of Total. As was already suggested, I tried
generating the individual records' percentage of total in a separate
query,
and then link the queries, but I still received a "subquery" error
message.
Hope this is helpful.

:

Can you tell us just a little bit more about what your data are? and
the
table structure? and the query SQL statement? and such?

--

Ken Snell
<MS ACCESS MVP>

I think this is so simple, yet alas...

What I am tring to do is create the expression: [Expr1]/sum[Expr1]
to
give
me percentage of total for individual records. Access gives me a
"subquery"
warning. Any workaround? As always, your insight is
appreciated!!!