Query multiple status

PG2015 · Jun 8, 2015

Hi, I am sure this is simple but cannot seem to get it right.

I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...

< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".

Help appreciated.

Thanks

jdraw · Jun 8, 2015

And what have you tried? Result?

You could try immediate iif statement

General syntax:

Code:

iif (yourvalue <500,"small[COLOR="Red"]"[/COLOR],
  iif(yourValue>=500 and yourValue<= 1000,"medium",
    iif(yourvalue >1000 and yourValue <=10000,"large",
	  iif(yourvalue>10000 and yourvalue<=100000,"very Large","????"))))

PG2015 · Jun 8, 2015

I spotted a missing quote mark on your word "small" and it works - brill!

you saved me a lot of time

Thanks for you help

jdraw · Jun 8, 2015

Thanks for finding the syntax error--- I've corrected the post.
Good luck with your your project.

namliam · Jun 8, 2015

I wouldnt hardcode an IIF personally.

Rather I would make a table to hold the ranges and the translated values, much easier to have maintained by non-access-savy users.

A simple table of MinValue - MaxValue - TextValue

vbaInet · Jun 8, 2015

For this sort of thing I would use a function, for legibility and performance reasons.

However, here's a different and even more efficient way:

* Create a table with the following structure and for the purpose of this explanation I'll call it tblProductRange:

Code:

Range1	Range2		Result
------	-----------	-----------
0	500		small
501	1000		medium
1001	10000		large
10001	100000		very large

* Add tblProductRange to your query and drop the Result field
* Open the query in SQL view and create a join in this format:

Code:

FROM TableName
     INNER JOIN tblProductRange 
     ON		TableName.FieldName >= tblProductRange.Range1
     AND	TableName.FieldName <= tblProductRange.Range2

But note that:
* You must fully qualify the field name with the table name as seen above
* You will get errors if you view it in Design View but don't worry about that. It just means that you're now restricted to using only SQL view within this query. To circumvent that, just create another query based on this one.

vbaInet · Jun 8, 2015

namliam said:
I wouldnt hardcode an IIF personally.

Rather I would make a table to hold the ranges and the translated values, much easier to have maintained by non-access-savy users.

A simple table of MinValue - MaxValue - TextValue

Pretty late with my response, but we're on the same page

namliam · Jun 8, 2015

What is it they say, great minds and all...

Though your solution isnt perfect (yet)

ranges shouldnt overlap, which you are doing bar at the 500

vbaInet · Jun 8, 2015

namliam said:
Though your solution isnt perfect (yet)

ranges shouldnt overlap, which you are doing bar at the 500

Ah don't worry, he gets the idea

Query multiple status

PG2015

Registered User.

jdraw

Super Moderator

PG2015

Registered User.

jdraw

Super Moderator

namliam

The Mailman - AWF VIP

vbaInet

AWF VIP

vbaInet

AWF VIP

namliam

The Mailman - AWF VIP

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread