If Statment within a query

aafroehlich

New Member
Joined
Dec 8, 2005
Messages
15
I have a query that is pulling data from a table. In the table i have a feild name "Num_Errs" and "Tot_Trans" and "Tier".

Tier could have one of 3 values: "Tier 1", "Tier 2" or "Tier 3".

Where Tier 1 = 98% error free Tier 2 = 97% error free and Tier 3 is everything else....

I need to say is give the value in "Num_Errs" if "Num_Errs" / "Tot_Trans is greater then 98% and falls into Tier 1. Else give me zero.

Can someone please help in do this...

Thanks,
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
Sorry can you explain that more clearly.:)

What value are you actually checking and where do you want the value returned?

Is the field Tier already populated?
 

aafroehlich

New Member
Joined
Dec 8, 2005
Messages
15
I am creating a report off of this query. I don't want to show Num_Errs in that report if they are in the accepted error range...

The Range is determined by Teirs... 98% or better for Tier 1, 97% or better for Tier 2, and any for Teir 3.

So what i need to do is divided number of errors by Tot_Trans to get a percentage that were errors.

If that percenate is less then the accapteable range for that Tier then i want the report to show 0 other wise give me the number of errors.

Hope this helps...
 

nullZero

Active Member
Joined
Nov 14, 2005
Messages
497
Create a new table in your database with the following values:

TIER RANGE
Tier 1 .98
Tier 2 .97
Tier 3 1

make sure that the RANGE field is defined as a Double not an Integer when you create the table, otherwise you won't be able to input decimals

After your new table is created join it to your existing one on TIER, then where you have your [Num_Errs]/[Tot_Trans] expression add the criteria <
#VALUE!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,633
Messages
5,832,780
Members
430,167
Latest member
Gogogoben

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top