How to group a number range in MS Access? Say >2 should always show as 1 in the query.

[JHJapay]

I wanted to display in my query the value of "1" for results ranging from 0 to 1.99999 or >2;
And then "2" for results ranging from 2 to 2.99999 or >3;
And then "3" for results ranging from 3 to 3.99999 or >4, and so on and so forth.

I am computing for salary increase for every 3 years that the employee served the company



Please help.

[alansidman]

Assumptions:
Field containing raw data is called Valuex
Query field to return results is called Calc
Table name is Table2

SQL statement

SELECT Table2.id, Table2.Valuex, Int([ValueX]) AS Calc
FROM Table2;

[June7]

Don't quite understand "from 0 to 1.99999 or >2". Should that be <2?

What results - a field in table?

[alansidman]

Relooking at your ask, I noticed an error in my original SQL Statement.

Revised:

SELECT Table2.id, Table2.Valuex, IIf([ValueX]<2,2,Int([ValueX])) AS Calc
FROM Table2;

[CarlettoFed]

Maybe it would be better like this:

SELECT Table2.id, Table2.Valuex, IIf([ValueX]<2,1,Int([ValueX])) AS Calc
FROM Table2;

[CJ_London]

I suspect you already have an answer but you really need to clear about your requirement

the value of "1" for results ranging from 0 to 1.99999 or >2; - so value of 4.1 would show 1 and a value of 4 would show 1
And then "2" for results ranging from 2 to 2.99999 or >3; - so value of 4.1 would show 2 and a value of 4 would show 2
And then "3" for results ranging from 3 to 3.99999 or >4, - so value of 4.1 would show 3 and a value of 4 would show? what?

4.1 can only be converted one value and I suspect it would be 4, but that is not clear from your description of the requirement. For the future, always better to provide examples that cover the range of possible values and most importantly, the outcome you want from that example data