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,
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,089
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,333
Members
412,717
Latest member
EVENTCATERING
Top