Lookup type question

[Age of madness]

Hi

Onto the next problem


I am currently trying to add in a lookup type query where if the figure is between 2 values then a second column will autofill with the corrospding figure.

so

Pts - Column1 - Column2

200 to 300 - 10 - 20
301 to 400 - 15 - 25
401 to 500 etc


What would be the best way to go about this?

cheers

 

[VicRauch]

Don't quite understand what you are asking for here. You are talking about two columns, and it looks like column 1 has a pair of values of 200 to 300, and then column 2 should populate with 10-20? What is the algarythm that dictates the 10-20 for 200 to 300? and then 301-400 gives 15-25? Could you try to explain this a little bit better?
Thanks,

 

[MyBoo]

I was going to suggest a simple Select Case statement but then realized that you want column2 to be a varying number, based on the varying number of column1. Kind of difficult to provide assistance when one doesn't have all the pertinent information.

Does the user update column1?

I would suggest you use a two column combobox and use code to place combobox.column(1) into your column2 field.

 

[Age of madness]

Sorry was a bit late when i wrote it, made a bit more sense to me at the time

Will try and clear up a bit better.

From a questionaire an individual ends up with a points total. this points total then relates to a point on a grade structure, so different point bandings will equal a different point on the structure.

The 2 columns represent 2 different schemes being used (so in all reality i could use the same formula/ expression and just change the point boundries).

e.g

person A scores a number of points, on the scheme being used anyone who scores between 200 and 300 would end up in grade 3 on scheme 1 and in grade 5 in scheme 2, anyone who scores between 301 and 400 would end up in grade 4 on scheme 1, and grade 7 on scheme 2 etc.

Hope that makes it a bit clearer.

 

[VicRauch]

What is the formula that produces grade 3 in scheme 1 if between 200 and 300? What is the formula that produces grade 5 in scheme 2 if between 200 and 300?
Or is there a table somewhere that just states these items. If it is a table, please show us some of the table. If it is a formula, please show us the formula.
Thanks,

 

[Age of madness]

havnt got to the formula stage on this one yet (this was partly what i was unsure on). I wasnt sure whether a Lookup type query was the best way, or if a series of IF Statements are required.

At the moment i have my main table which contains the points total and a column for scheme 1 and a column for scheme 2. I currently have a table for each scheme which contains an ID and the list of grades that make up the scheme.

sorry if im not explaing this very well, this is the first time ive tried this in access. Nearest comparator i could give would be the Vlookup in excel, but am not sure how this would be expressed in access

 

[VicRauch]

At the moment i have my main table which contains the points total and a column for scheme 1 and a column for scheme 2. I currently have a table for each scheme which contains an ID and the list of grades that make up the scheme.

Please, please, please, let us see what you have. You say you have a main table which contains points total in a column for scheme1 and another for scheme 2, then you have a table for each scheme which contains an ID and the list ofgrades that make up the scheme. We have NO idea what is in these tables. Show us so we can help.

 

[Age of madness]

Hi Vic

Sorry for driving you the wall with this one. Seen the answer in one of the posts today http://www.mrexcel.com/board2/viewtopic.php?t=239203 (IIF statement)

for future ref when you say see the tables im assuming we can not upload. is there a certain layout that is used on the boards for this?

cheers for the help (although someone else made the same question much easier to understand )

 

[VicRauch]

REPORT 1 - "CLASS" INFORMATION
PRODUCT WEIGHT CLASS
Product 1 100 A
Product 2 150 B
Product 3 200 B

REPORT 2 - "SUM OF WEIGHTS" INFORMATION
CATEGORY TOTAL WEIGHT
NEW 1200
USED 2000

This is how the tables where shown to the board in the thread you referenced. An even better way to show this would be:

REPORT 1 - "CLASS" INFORMATION
Product.......Weight....Class
Product 1 ....100 ........A
Product 2 ....150 ........B
Product 3 ....200 ........B

REPORT 2 - "SUM OF WEIGHTS" INFORMATION
Category....TotalWeight
NEW..............1200
USED.............2000

By spacing out the column titles, showing the titles in Upper/Lower case with no blanks in column names, and spacing out the sample data until it shows under the correct column name, it does make it easier to see. The web pages that we are viewing this stuff on will only allow 1 blank space between words, unless you code the " " in the source. Therefore, use periods to space the data and column headings out.
BTW, this sample is really not a good one in that in report 1, Category is a letter ("A", "B") and in report 2 Category is "NEW" or "USED"

 

[SydneyGeek]

Access doesn't have a VLOOKUP equivalent built in.

A guy called Allen Browne has created ELOOKUP (Extended Lookup), a custom function for Access with similar functionality. Check it out

Denis