CODEWITHSUNDEEP
sqlms-access
JosephD
JosephD

Reputation: 119

Combining Conditions in Access SQL Query

I need to create a SQL query in Access that picks a single value from a range of numbers based on a particular source value. But, there's a special logical quirk. If the table has numbers lower than the source value, the query should return the greatest number that's equal to or less than the source value. However, if the source value is lower than all the values in the table, the query should return the smallest value in the table. In other words, if the values in the table were

2000
1900
1800
1700

and the source value was 1975, the query should return 1900; but if the source value is 1632, the query should return 1700. The query should return only a single value that meets either the one condition, or the other if the first condition fails.
I'm not sure how I can combine the two conditions in Access SQL, and I haven't been able to find a similar question here. Thanks in advance for any help.

Upvotes: 0

Views: 106

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use conditional aggregation:

select nz(max(iif(col <= 1975, col, null)),
          min(col)
         )
from t;

1975 is whatever value you want to test.

Upvotes: 2

forpas
forpas

Reputation: 164089

You can do it with conditional sorting and then pick the top row:

SELECT TOP 1 col
FROM tablename
ORDER BY (col <= 1975), ABS(col - 1975)

The expression col <= 1975 is evaluated as -1 for true and 0 as false, so the values of col that are less or equal to 1975 will be first (if they exist).
Finally the expression ABS(col - 1975) will put the closest value to 1975 at the top and TOP 1 will pick that.

Upvotes: 1

dougp
dougp

Reputation: 3087

So, if a < min(b) then min(b) else max(b) that is less than a.

Here it is in SQL. This uses a correlated subquery. I don't have recent experience in Access, but it must be possible.

create table a (
a int not null
)
create table b (
b int not null
)

insert a
values (1975), (1632)

insert b
values (2000), (1900), (1800), (1700)

select a.a
, case
    when a.a < (select min(b) from b)
      then (select min(b) from b)
    else (select max(b.b) from b where b.b <= a.a)
  end as b

from a

Upvotes: 0

Related Questions