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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|