Bread down numbers by categories based on value with uneven steps

[Guest]

Hello

How can I break up a column of numbers by categories with uneven steps
without going to long iif statement

Thank you for your help

 

[jahoobob via AccessMonster.com]

?

Hello

How can I break up a column of numbers by categories with uneven steps
without going to long iif statement

Thank you for your help

 

[Guest]

Not enough info. Where do you want to do this, report, query, ?

 

[Guest]

I want to do it in a query.
I have a list categories based on value in the first column
for example
<25,000
25000< but 50000
but steps are not even
sorry for confusion

 

[John Vinson]

Hello

How can I break up a column of numbers by categories with uneven steps
without going to long iif statement

Thank you for your help

A "Range" table can be useful here: three fields, Low, High, Category.
E.g.

0 63 F
64 72 D
73 80 C
80 88 G
89 100 A

You can then create a "Non Equi Join" query:

SELECT mytable.this, mytable.that, mytable.score, Ranges.Category
FROM mytable
INNER JOIN Ranges
ON Ranges.Low <= mytable.score
AND Ranges.High >= mytable.score;

John W. Vinson[MVP]

 

[John Nurick]

One way is by using the Switch() function in a calculated field in a
query, e.g. this converts values in the Score field into categories:

Category: Switch([Score]<40,"F", [Score]<50, "D", [Score]<65, "C",
[Score]<75, "B", [Score]<85, "A", [Score]>=85, "A+")

Switch takes pairs of arguments. Starting with the first pair, it
evaluates the first argument: if it evaluates to True or -1, it
evaluates and returns the value of the second argument; if the first
argument does not evaluate to True, Switch starts over with the next
pair of arguments.

(For more help on Switch(), open the VBA editor and type
Switch Function
into the help box.)

 

[Guest]

Thanks a lot
It worked

A "Range" table can be useful here: three fields, Low, High, Category.
E.g.

0 63 F
64 72 D
73 80 C
80 88 G
89 100 A

You can then create a "Non Equi Join" query:

SELECT mytable.this, mytable.that, mytable.score, Ranges.Category
FROM mytable
INNER JOIN Ranges
ON Ranges.Low <= mytable.score
AND Ranges.High >= mytable.score;

John W. Vinson[MVP]