If Statment within a query

[aafroehlich]

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,

 

[Norie]

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]

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]

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!