Expression for Percentage Calculations Based Upon Subsets of Field

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!


0
Utf
1/13/2008 10:47:03 PM
access 16762 articles. 3 followers. Follow

4 Replies
620 Views

Similar Articles

[PageSpeed] 5

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

"Inuchan" <Inuchan@discussions.microsoft.com> wrote in message 
news:98F09FC4-51D5-421C-A9D4-90B54E380CEE@microsoft.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!
>
> 


0
Arvin
1/14/2008 12:36:28 AM
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?

"Arvin Meyer [MVP]" wrote:

> 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
> 
> "Inuchan" <Inuchan@discussions.microsoft.com> wrote in message 
> news:98F09FC4-51D5-421C-A9D4-90B54E380CEE@microsoft.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
0
Utf
1/14/2008 1:41:01 AM
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!

"Inuchan" wrote:

> 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?
> 
> "Arvin Meyer [MVP]" wrote:
> 
> > 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
> > 
> > "Inuchan" <Inuchan@discussions.microsoft.com> wrote in message 
> > news:98F09FC4-51D5-421C-A9D4-90B54E380CEE@microsoft.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
0
Utf
1/14/2008 2:18:01 AM
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

"Inuchan" <Inuchan@discussions.microsoft.com> wrote in message 
news:74263D6E-7838-4AC4-B3AB-D474D44A27CD@microsoft.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!
>
> "Inuchan" wrote:
>
>> 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?
>>
>> "Arvin Meyer [MVP]" wrote:
>>
>> > 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
>> >
>> > "Inuchan" <Inuchan@discussions.microsoft.com> wrote in message
>> > news:98F09FC4-51D5-421C-A9D4-90B54E380CEE@microsoft.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 


0
Arvin
1/14/2008 4:30:25 AM
Reply:

Similar Artilces:

Resource Units
I want to be able to have a resource, say 'electrician', with 5 available for the day shift and 2 available for the night shift. This would allow e to just assign the 'electrician' resource and have project level based on unit availability. If I setup 'electrician Day' and 'electrician Night', then I need to carefully select which to use and if the schedule shifts it is a nightmare. Any thought on how to do this? Thank-you! On Jan 6, 10:43=A0pm, John K <John K...@discussions.microsoft.com> wrote: > I want to be able to have a resou...

Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and deposits. Current balance is determined by subtracting withdrawals and adding deposits to the balance in the line above. Suddenly I have started getting a cell entry #VALUE! in the current balance cell. A typical entry in the cell is for G43 would be: =G42-E43+F43 and the sudden new result is #VALUE. All three columns have cells formatted as numbers. HELP! On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote: > I have a spreadsheet with three columns: Current balance, withdrawals, and > deposit...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

% calculation
This always confuses me... What formula for 15% of 40,000 thanks Oooops, should have mentioned cells.. This always confuses me... What formula for 15% (L7) of 40,000 (L8) and answer in L9 thanks "S" <nonense@blueyonder.co.uk> wrote in message news:NZ9Hj.28495$w51.14942@fe2.news.blueyonder.co.uk... > This always confuses me... > What formula for 15% of 40,000 > > thanks > This is a multi-part message in MIME format. --------------070605030800000709050004 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit...

moving messages from outlook express to outlook
I am trying to move messages from outlook express version 6 to outlook 2003. When I export from outlook express, there is no indication where the export file is stored. Is there a way to move messages or will I have to email them to myself? Thanks ...

Required fields
I need to set up some VB code to alert the inputter that they need to complete a couple of required fields depending on the value of a third field. At present I have some code (below) attached to the BeforeUpdate event of the form that checks for one of the fields but I need to extend it to the 2nd field and I don't know how to. Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And Me.New_Cease = "New" Then MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be saved&q...

How do I format a field based on the value
I have a form that is populated from a query using several tables. In the detail section I have the following fields: Category Code qty PO number ... .... ... The qty is populated in the query based on whether or not a PO number is available. If the PO number is not available then it comes from table a, if it is available then it comes from table b. On the form, I have my fields color-coded for easy reading. I would like to be able to color the border and column heading based on where this value comes from. If table A, then it is blue, if table b then it...

Trouble with Forms based authentication in OWA
I am having some problems in setting up OWA with forms based authentication. From my understanding it should work like this: I go to my usual address http://<myserverdomain>/Exchange and I will be directed to the Forms Based Authentication login screen. Once I am there I can choose basic or premium function in OWA and once I authenticate I should be able to access OWA without any other login prompts. Well none of that is happening. Here is what I have done so far. 1. Enabled Forms based authentication in Exchange System manager. 2. Verified that that Authenticated Users group has Read...

hyperlinks in Outlook Express
Just wondering if there is a way to set Outlook Express to open hyperlinks. They appear active, but when I click on them, nothing happens. I use IE for internet access. Any help would be appreciated. Thanks! this is an outlook newsgroup, not an outlook express newsgroup, try one o them ! N "Lynn" <anonymous@discussions.microsoft.com> wrote in message news:08a101c3cf1b$85e4b210$a001280a@phx.gbl... > Just wondering if there is a way to set Outlook Express > to open hyperlinks. They appear active, but when I click > on them, nothing happens. I use IE for internet acce...

Round up a decimal in a query field
Hopefully this is an easy question. How do you round up a number in a query field? Excel has the ROUNDUP function, but I can't find anything similar in Access. Can anyone help? Function:round([tablename!fieldname,2]) I think. It may be round([tablesname!fieldname],2) "bigomega73" wrote: > Hopefully this is an easy question. How do you round up a number in a query > field? Excel has the ROUNDUP function, but I can't find anything similar in > Access. Can anyone help? Thanks Golfinray, but that only rounds to the nearest decimal place. What I want is the numb...

Import Messages from Outlook Express 6
Hi, I would like some advice on importing messages that are currently on a Win98se machine running OE 6 into an XP Pro machine running Outlook 2003, Thanks, any help greatly appriciated, Ed <anonymous@discussions.microsoft.com> wrote in message news:5c9c01c3e5bb$834ea400$a001280a@phx.gbl... > Hi, > I would like some advice on importing messages that are > currently on a Win98se machine running OE 6 into an XP > Pro machine running Outlook 2003, > Thanks, any help greatly appriciated, > Ed Go here to backup your old OE: http://insideoe.tomsterdam.com/backup/index.htm...

Query-based distribution group anomaly
When I do a TOOLS ~ OPTIONS ~ DELEGATES in Outlook 2003 and try to set a query-based distribution group as a delegate it seems to accept the group and whatever permissions I assign. But as soon as I click OK and then come back in to inspect it, the query-based distribution group has no permissions at all. This happens every time. However, in the same situation, whenever I assign a Global Security Group as a delegate and assign permissions, they stick. Should my query-based distribution group be accepted or is this kind of group not permitted as a delegate? But then if it's not perm...

SQL to update Item.LastCounted based on PhysicalInventory.CloseTime
Because of a bug/deficiency/oversight in a third party inventory counting application , I did a bunch of physical inventories that did not update the Item.LastCounted field. I need this field updated so I can see the correct date each item was counted last. I am willing to do a seperate SQL statement for each physical inventory sheet for which I have the problem. For example, I want to take each item in the PhysicalInventoryEntry table with PhysicalInventory.Code = '0000057' (or whatever the code is) and set Item.LastCounted = PhysicalInventory.CloseTime. I am not great at SQ...

How to export address book from Outlook XP to Outlook Express?
Does anyone know how I can export my Address Book from Outlook to Outlook express? Outlook Address Book is a .pab file. In outlook express click on File / Import / Other address books / Select outlook .pab file and you should be all set. I hope this helps thanks! "Candice" <bowns_candice@hotmail.com> wrote in message news:00a101c3cfba$7878b870$a101280a@phx.gbl... > Does anyone know how I can export my Address Book from > Outlook to Outlook express? > > Candice <bowns_candice@hotmail.com> wrote: > Does anyone know how I can export my Address Book f...

Export News from Outlook express and import into windows mail
I only used outlook express for newsgroup so I need to backup and transfer all the messages that I flagged with the glasses icon. I just installed windows mail and it only downloaded the first 1000 messages. I can't see all of my messages because some of them they are old ... maybe 2002 but i need them all in the same place. How can I transfer these messages and the replies to windows mail? Outlook Express as well as other newsreaders only mirror what is on a news server and newsgroups neither import nor export. You can only export the account setting, but not groups or...

how to retrieve varbinary field through ADO inVC++?
...

Calculated Fields
Is there a way to use an IF or SUMIF function in a calculated field in a pivot table? When I try, I get all 0s as the result. Can you type a small sample of the pivot table layout and data, and explain what you'd like to calculate with a SUMIF formula? Jim C. wrote: > Is there a way to use an IF or SUMIF function in a > calculated field in a pivot table? When I try, I get all > 0s as the result. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi Jim, > Is there a way to use an IF or SUMIF function in a > calculated f...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

Keep Added Custom Fields on New Projects
We are importing information from our bidding software. The export is created in XML file. We know the custom fields that are created. How do I make MS Project use the same "look" everytime? I want it to remember the custom fields I created and use that as my default blank project. I don't care about the tasks because those could be different every time. I just want to be able to create each project with the same columns every time. Create or update a view/table with the custom fields and layout you are wanting. Then change the Tools-->Options--View tab to ...

Populating an IMAP "To:" field
I let this bug me for almost 2 years, but it *IS* Possible, as the following points out (contrary to the lack of responses and denial posted elsewhere in these microsoft misinformation forums): For Outlook 2000/XP/2002: http://support.microsoft.com/?kbid=284371 For Outlook 2003: 1) Select the IMAP mail folder with the broken/empty "To:" field. 2) Go to the "Tools" menu and select "Rules and Alerts..." 3) Select the IMAP account for this folder (should be pre-selected) 4) Click the "New Rule..." button 5) Select the "Start from a blank rule" r...

Money using wrong date to calculate ROI YTD
I have opened a new file in Money to track investments for a stock club. I entered all transactions, which go back to Jan. 2002. When I look at performance for the account, the ROI YTD is not accurate, as Money is using values for the stocks going back only 6 months form the date I enterd the transactions rather than the values as of 1/1/03. What to do? In microsoft.public.money, Brooks wrote: >I have opened a new file in Money to track investments for >a stock club. I entered all transactions, which go back >to Jan. 2002. When I look at performance for the account, &g...

Undeleting a folder in Express
Can anyone direct me to a free software download that will allow me to undelete a folder in Outlook Express or am I stuck purchasing the required software? This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer.ie5.outlookexpress&a...

Outlook Express Imported Email
I've recently imported mail from Outlook express to Outlook 2007. The "From" fied of all the old imported email does not display the email address. It just shows the sends full name. I therefore cannot reply to old email or click on the From field and add the email address to my contacts. Anyone come across this problem before? thanks, "Pryan" <Pryan@discussions.microsoft.com> wrote in message news:8B86DF03-5BC4-40B3-ADE4-F73674833190@microsoft.com... > I've recently imported mail from Outlook express to Outlook 2007. It's be...

auto populating account id field
Does anyone know of any 3rd parties that already have this customization? I'm wanting my account to automatically be assigned an account ID when I transfer my lead to an account and then have this ID be sent to Great Plains to create the account in GP. I know CRM doesn't have this function right out of the box, but I can't let my users type in their own ID. Thanks, Jen ...

Sum of calculated controls in footers
I have a report with the following groupings: 1.) Claimant 2.) Group 3.) Carrier in the Claimant footer I have the a control named max_ded with the following calculation =Max([Ded]). I need to be able to sum this field at the "group level", and then sum the "group level sum" at the carrier level. I have tried to sum(max([Ded])) and I get error messages. I have tried Sum([max_ded]) etc. Any ideas would be greatly appreciated. Thanks ...