Category problem

[Thomp001]

I need to run a query that will give me "NE" as the answer when my field meets certain criteria. I need it to say "NE" when the field has a value between 5 and 200 and "Formal" when the value is between 200 and 2000.

Here is the query as it already has some filters build in to it.

SELECT Mission2.AIRBILL_NBR, Mission2.TRACKING_NBR, Mission2.entry_type_cd, Mission2.pri, Mission2.rls, Mission2.cst, Mission2.PLS, "NE" AS dest_total_customs_amt, Mission2.bts_reason
FROM Mission2
WHERE (((Mission2.rls) Is Null) AND ((Mission2.PLS) Is Not Null) AND (("NE") >5 <200))
ORDER BY "SI";

Thanks in advance for any help you can give me on this.

Thomp

 

[Jon K]

We need more information.


What is the field name of 'the field'?

The given two ranges overlap. Do you mean:
between 5 and 200
between 201 and 2000?

What would you like it to say when the value in the field is <5, >2000, or Null?

What is "SI" in the Order By clause? It is one of the fields in the table?

 

[Thomp001]

Category

The name of the field is "dest_total_customs_amt" .

The critera shold not be overlapping it needs to be >5 and less 200. This would be named "NE".

If the value is greater than 200.01 or equal to or less than 2000 then it would be named "SI".

If it is over 2000 then it would be named "EDR".

Less than 5 would be "DF"

The field of "dest_total_customs_amt" would need to be in desending order.

Thanks,
Thomp

 

[Jon K]

Type/paste in the SQL View of a new query:-

SELECT Mission2.AIRBILL_NBR, Mission2.TRACKING_NBR, Mission2.entry_type_cd, Mission2.pri, Mission2.rls, Mission2.cst, Mission2.PLS,
Mission2.dest_total_customs_amt,
IIf([dest_total_customs_amt]<5,"DF",IIf([dest_total_customs_amt]>=5 And [dest_total_customs_amt]<200,"NE",IIf([dest_total_customs_amt]>=200 And [dest_total_customs_amt]<2000,"SI","EDR"))) AS Category,
Mission2.bts_reason
FROM Mission2
WHERE Mission2.rls Is Null AND Mission2.PLS Is Not Null
ORDER BY dest_total_customs_amt Desc;


Note:
I have replaced the "NE" parts of your SQL statement with an expression for Category and set the descending sort order.


Your post has skipped the values =5, =200 to <=200.01, and =2000, e.g.
Less than 5 would be "DF"
>5 and less 200. This would be named "NE".

Here =5 has been skipped.

In the Category expression, I have included =5 in the NE category, =200 to <=200.01 in SI, and =2000 in EDR. If these are not what you desired, you can easily alter the < and >= signs to include them in the correct categories.

 

[Thomp001]

Category

Jon

Thanks a million.. Works like a charm