Lookup type question

Age of madness

New Member
Joined
Oct 18, 2006
Messages
9
Hi

Onto the next problem :biggrin:


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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
Aug 9, 2006
Messages
217
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

New Member
Joined
Oct 18, 2006
Messages
9
Sorry was a bit late when i wrote it, made a bit more sense to me at the time :biggrin:

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

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

New Member
Joined
Oct 18, 2006
Messages
9
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

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

New Member
Joined
Oct 18, 2006
Messages
9
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,136,267
Messages
5,674,730
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top