Best method to map and populate a new field that is based on a range of values in an existing field

[Dave K]

Hi - I am trying to assign or map a score in a field called "Weight" of 1 to 5, based on an existing field (called Rank) that has values of 1 to 4 million.

So, for example, Values in the 'Rank' Field that range from 1 to 100,000 need to be assigned a value of 5 in the 'Weight' field, and Values in the 'Rank' Field that range from 100,000 to 500,000 need to be assigned a value of 4 in the 'Weight' field, etc.

In Excel, I am able to accomplish this using the Lookup Function or by using the Index and Match function.

However, I am trying to determine the best method to do this in Access. I suppose I could develop a very long IIF statement, but I was hoping there is a simpler way that would allow me to create a table with the ranges, and then somehow populate the results in the field titled "Weight" by running an update query (or something like that).

Does anyone know of a simple and efficient way to do this? Thanks for any advise.

[John W. Vinson]

Post by Dave K
Hi - I am trying to assign or map a score in a field called "Weight" of 1 to 5,
based on an existing field (called Rank) that has values of 1 to 4 million.
So, for example, Values in the 'Rank' Field that range from 1 to 100,000
need to be assigned a value of 5 in the 'Weight' field, and Values in the
'Rank' Field that range from 100,000 to 500,000 need to be assigned a
value of 4 in the 'Weight' field, etc.
In Excel, I am able to accomplish this using the Lookup Function or by
using the Index and Match function.
However, I am trying to determine the best method to do this in Access.
I suppose I could develop a very long IIF statement, but I was hoping there
is a simpler way that would allow me to create a table with the ranges, and
then somehow populate the results in the field titled "Weight" by running an
update query (or something like that).
Does anyone know of a simple and efficient way to do this? Thanks for any advise.

Well, you answered your own question. Create a five row table with three
fields, Weight, Low and High:

Weight Low High
5 0 100000
4 100001 500000

and so on; then create a Query in SQL view (the query grid can't do this kind
of join but SQL can):

SELECT Weights.Weight, yourtable.Rank, <any other fields>
FROM yourtable INNER JOIN Weights
ON yourtable.Rank > Weights.Low
AND yourtable.Rank <= Weights.High;

Note that since the Weight can be calculated on demand using this Query, it
should not be stored ANYWHERE, in ANY table - just use the Query.

Note that this newsgroup was killed off by Microsoft some years ago, and
there's only a few of us troglodytes still checking it occasionally (yours is
the first post I've seen in a month or so). See my .sig for more active venues
to get your questions asked!

[Ammammata]

Il giorno Mon 15 Jun 2015 06:38:58p, *Dave K* inviava su

Post by Dave K
I suppose I could develop a very long IIF statement, but I was hoping
there is a simpler way that would allow me to create a table with the
ranges, and then somehow populate the results in the field titled
"Weight" by running an update query (or something like that)

...or you can write some code and use the statement CASE

[Larry Linson]

On Mon, 15 Jun 2015 09:38:58 -0700 (PDT), Dave K

Post by Dave K
Hi - I am trying to assign or map a score in a field called "Weight" of 1 to 5, based on an existing field (called Rank) that has values of 1 to 4 million.
So, for example, Values in the 'Rank' Field that range from 1 to 100,000 need to be assigned a value of 5 in the 'Weight' field, and Values in the 'Rank' Field that range from 100,000 to 500,000 need to be assigned a value of 4 in the 'Weight' field, etc.
In Excel, I am able to accomplish this using the Lookup Function or by using the Index and Match function.
However, I am trying to determine the best method to do this in Access. I suppose I could develop a very long IIF statement, but I was hoping there is a simpler way that would allow me to create a table with the ranges, and then somehow populate the results in the field titled "Weight" by running an update query (or something like that).
Does anyone know of a simple and efficient way to do this? Thanks for any advise.

Take a look at the Switch function in Access. Details can be found at:

http://www.techonthenet.com/access/functions/advanced/switch.php

Embed it in the Query with you to retrieve and use the information to
create a calculated field when you need it. Defining and populating a
field in the table when it can be calculated when needed is called
"committing spreadsheet" and is not necessary in a database
environment.

I used a Query, the SQL for which is:

SELECT Original.ID, Original.Rank,
Switch([Rank]>500000,99,[Rank]<1,0,[Rank]<100001,5,[Rank]>100000,4) AS
Weight
FROM Original;

To test, and it appears to work.

I created a Table named Original, with an (Autonumber) ID, Rank, and
some Text. Since you did not address boundary conditions in your post,
the Switch statement is based on returning a 0 if the Rank is less
than 1, the range 1 - 100000 is inclusive, that the other range of
interest is 100001 - 500000, and anything above will return a Weight
of 99. You can modify as needed, see below.

You need to clarify to yourself whether the value ranges "1 to 4
minillion" as you said in the first sentence or are 1 to 100,000 and
100,000 - 500,000 as you say in the second sentence or you also have
to determine what you do with values below the low value of the
ranges, higher than the high value, and what the Weight should be if
the value is exactly on the dividing point (e.g., in sentence 2, that
would seem to be 100,000).

My tiny test DB was done in Access 2010, but I've used the Switch
built-in function in earlier versions, as well.

Larry Linson
Microsoft Access MVP 2003 - 2014
Co-Author, Microsoft Access Small Business Solutions,
pub by Wiley, 2010