Expression for Percentage Calculations Based Upon Subsets of Field

[Inuchan]

How do you create an expression that will calculate a percentage based upon
subsets of two fields?

For example, one field is "Status" (there are three options), the other is
"Resolved" (populated with a "Yes" in the appropriate cells that correspond
to those "Status" entries that are resolved. I must calculate the percentage
of "Resolved" entries for each status classification based upon the total
number of each status classification.

I have created parameter queries that appropriately identify the number of
matching records for each Status classification with those that have been
resolved, but I do not know how to transfer this data to an expression that
automatically calculates those percentages.

Any insight would be greatly appreciated. Thanks!

 

[Arvin Meyer [MVP]]

Use a query with 3 extra columns for Status and add an expression like this
in each:

Option1: IIf([Status] = 1, 1, 0)
Option2: IIf([Status] = 2, 1, 0)
etc.

That will give you an entry of 1 for each of the options. Now build a second
query, to get the total of each column. You should now be able to get the
percentage of each.

 

[Inuchan]

Thank you so very much for your help. I have created the query, but now it is
asking for a parameter values for each of the options. Any suggestions?

Use a query with 3 extra columns for Status and add an expression like this
in each:

Option1: IIf([Status] = 1, 1, 0)
Option2: IIf([Status] = 2, 1, 0)
etc.

That will give you an entry of 1 for each of the options. Now build a second
query, to get the total of each column. You should now be able to get the
percentage of each.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How do you create an expression that will calculate a percentage based
upon
subsets of two fields?

For example, one field is "Status" (there are three options), the other is
"Resolved" (populated with a "Yes" in the appropriate cells that
correspond
to those "Status" entries that are resolved. I must calculate the
percentage
of "Resolved" entries for each status classification based upon the total
number of each status classification.

I have created parameter queries that appropriately identify the number of
matching records for each Status classification with those that have been
resolved, but I do not know how to transfer this data to an expression
that
automatically calculates those percentages.

Any insight would be greatly appreciated. Thanks!

T

 

[Inuchan]

I have produced a query that looks like it should produce the appropriate "1"
in each column; however, it is giving #error in each of the columns created
for each of the status options.

To clarify, the query should be one that identifies the Resolved field as
"yes" and the the parameter option for "status." Then I add the three extra
columns, and at the top, I enter the expressions that you suggested?

Thanks!

Thank you so very much for your help. I have created the query, but now it is
asking for a parameter values for each of the options. Any suggestions?

Use a query with 3 extra columns for Status and add an expression like this
in each:

Option1: IIf([Status] = 1, 1, 0)
Option2: IIf([Status] = 2, 1, 0)
etc.

That will give you an entry of 1 for each of the options. Now build a second
query, to get the total of each column. You should now be able to get the
percentage of each.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How do you create an expression that will calculate a percentage based
upon
subsets of two fields?

For example, one field is "Status" (there are three options), the other is
"Resolved" (populated with a "Yes" in the appropriate cells that
correspond
to those "Status" entries that are resolved. I must calculate the
percentage
of "Resolved" entries for each status classification based upon the total
number of each status classification.

I have created parameter queries that appropriately identify the number of
matching records for each Status classification with those that have been
resolved, but I do not know how to transfer this data to an expression
that
automatically calculates those percentages.

Any insight would be greatly appreciated. Thanks!

T

 

[Arvin Meyer [MVP]]

Yes. And BTW, if it is asking for parameters, it means that something may be
misspelled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have produced a query that looks like it should produce the appropriate
"1"
in each column; however, it is giving #error in each of the columns
created
for each of the status options.

To clarify, the query should be one that identifies the Resolved field as
"yes" and the the parameter option for "status." Then I add the three
extra
columns, and at the top, I enter the expressions that you suggested?

Thanks!

Thank you so very much for your help. I have created the query, but now
it is
asking for a parameter values for each of the options. Any suggestions?

Use a query with 3 extra columns for Status and add an expression like
this
in each:

Option1: IIf([Status] = 1, 1, 0)
Option2: IIf([Status] = 2, 1, 0)
etc.

That will give you an entry of 1 for each of the options. Now build a
second
query, to get the total of each column. You should now be able to get
the
percentage of each.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How do you create an expression that will calculate a percentage
based
upon
subsets of two fields?

For example, one field is "Status" (there are three options), the
other is
"Resolved" (populated with a "Yes" in the appropriate cells that
correspond
to those "Status" entries that are resolved. I must calculate the
percentage
of "Resolved" entries for each status classification based upon the
total
number of each status classification.

I have created parameter queries that appropriately identify the
number of
matching records for each Status classification with those that have
been
resolved, but I do not know how to transfer this data to an
expression
that
automatically calculates those percentages.

Any insight would be greatly appreciated. Thanks!




T