Expression for Percentage Calculations Based Upon Subsets of Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Inuchan - 13 Jan 2008 22:47 GMT

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!

Reply to this Message

Arvin Meyer [MVP] - 14 Jan 2008 00:36 GMT

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.

Signature

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
[quoted text clipped - 15 lines]
>
> Any insight would be greatly appreciated. Thanks!

Reply to this Message

Inuchan - 14 Jan 2008 01:41 GMT

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:
[quoted text clipped - 27 lines]
>
> T

Reply to this Message

Inuchan - 14 Jan 2008 02:18 GMT

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?
[quoted text clipped - 30 lines]
> >
> > T

Reply to this Message

Arvin Meyer [MVP] - 14 Jan 2008 04:30 GMT

Yes. And BTW, if it is asking for parameters, it means that something may be
misspelled.

Signature

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"
[quoted text clipped - 53 lines]
>> >
>> > T

Reply to this Message