A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Unbound Calculation populates from 3 seperate fields



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2010, 06:03 AM posted to microsoft.public.access.reports
DAShadow
external usenet poster
 
Posts: 1
Default Unbound Calculation populates from 3 seperate fields

Hope someone can help me solve this...I really need some help, I have tried
and read alot and can not find the answer I am looking for.

I am trying to get this expression to work in my database.
I have 3 fields QC%, InBound% and 3rd Party% that have percentages.
using a bound field:
( I have used IIF and NZ to assist with Null values)
QC%=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed
Audits],'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100

InBound%=IIf(nz([SumOfInBound Total],'0')=0,0,Sum(nz([SumOfInBound Passed
Audits],'0'))/Sum(nz([SumOfInBound Total],'0'))*100

3rd Party%IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd
Party Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100

I am trying to get these to add together by using a unbound field:

=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed Audits]
,'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100\1+IIf(nz([SumOfInBound
Total],'0')=0,0,Sum(nz([SumOfInBound Passed Audits],'0'))/Sum(nz(
[SumOfInBound Total],'0'))*100/1
+IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd Party
Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100\1

I added \1 into my formula to move to each of the next fields (QC%, InBound%
and 3rd Party% to add it properly...which actually works BUT now I need to
divide by each field. I need to find out how to ask it to move to each field
if there is a 0 or a value so that I can divide it to get a Overall total.
If I have 3 values this works fine but when I only have 1 value it is
dividing by 3 when actually there is only 1 value...the rest are 0's.

I am adding these 3 fields together to get one number that is a Overall
Percentage for each field (QC%,InBound% and 3rd Party%).

Is there something I add to this formula...a wildcard?? Please help!

Thanks,
DASH

  #2  
Old May 26th, 2010, 02:15 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Unbound Calculation populates from 3 seperate fields

At least part of your problem is that you are returning text values instead of
number values in your expressions. The quotes around the zeros turn them into
text values and if the IIF statement has a requirement to return a string for
any of its arguments it will convert all the return values as strings.

QC%=IIf(nz([SumOfQC Total Number Audited],0)=0,0,Sum(nz([SumOfQC Passed
Audits],0))/Sum(nz([SumOfQC Total Number Audited],0))*100

InBound%=IIf(nz([SumOfInBound Total],0)=0,0,Sum(nz([SumOfInBound Passed
Audits],0))/Sum(nz([SumOfInBound Total],0))*100

3rd Party%=IIf(Sum(nz([SumOf3rd Party No Audits],0))=0,0,Sum(nz([SumOf3rd
Party Passed Audits],0))/Sum(nz([SumOf3rd Party No Audits],0))*100

Adding the percentages together and dividing by 3 to get an overall percentage
seems a bit suspicious to me as a reliable measure unless all three items were
of equal importance and of equal weight.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

DAShadow wrote:
Hope someone can help me solve this...I really need some help, I have tried
and read alot and can not find the answer I am looking for.

I am trying to get this expression to work in my database.
I have 3 fields QC%, InBound% and 3rd Party% that have percentages.
using a bound field:
( I have used IIF and NZ to assist with Null values)
QC%=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed
Audits],'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100

InBound%=IIf(nz([SumOfInBound Total],'0')=0,0,Sum(nz([SumOfInBound Passed
Audits],'0'))/Sum(nz([SumOfInBound Total],'0'))*100

3rd Party%IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd
Party Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100

I am trying to get these to add together by using a unbound field:

=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed Audits]
,'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100\1+IIf(nz([SumOfInBound
Total],'0')=0,0,Sum(nz([SumOfInBound Passed Audits],'0'))/Sum(nz(
[SumOfInBound Total],'0'))*100/1
+IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd Party
Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100\1

I added \1 into my formula to move to each of the next fields (QC%, InBound%
and 3rd Party% to add it properly...which actually works BUT now I need to
divide by each field. I need to find out how to ask it to move to each field
if there is a 0 or a value so that I can divide it to get a Overall total.
If I have 3 values this works fine but when I only have 1 value it is
dividing by 3 when actually there is only 1 value...the rest are 0's.

I am adding these 3 fields together to get one number that is a Overall
Percentage for each field (QC%,InBound% and 3rd Party%).

Is there something I add to this formula...a wildcard?? Please help!

Thanks,
DASH

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.