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

 

[Age of madness]

Tried the IIF way of doing it today, had too many statements for it to run (came up saying it ws too complex). So back to square 1.

Starting over again. Right current set of tables i have are:

QUESTIONNAIRES
ID.......TITLE........QUES 1..........QUES 2
na01....Admin...........2..................4

SCHEME 1 (this is set up i the same way as a Vlookup array in excel)
SCORE...........LEVEL
150...................5
151...................5
152...................5
...
199...................5
200..................10
201..................10

And so on, the score field goes up to 1000, and there are a total of 14 levels within that scale. so 200 points would be level 5, between 200 and 250 = level 6 etc.

these 2 tables are not linked, the link is created through the query below.

QUESTIONNAIRES QUERY

ID.......title......Q1.........Q1 Score....Q2.....Q2Score....points....Scheme
na01...Admi......3..............62...........6........165...........227......

the Q1 and Q2 score fields are created via calculations within the query, as is the Points field adding all the score fields.

What i am trying to do is where there is a correlation between the POINTS field in the query and the SCORE field in the SCHEME 1 table it will return the value in the LEVEL field from SCHEME 1 and place it into the SCHEME field in the query.

Hopefully this makes more sense for you guys. Am i on the right track with this logic, or am i trying to do things the long way around?

on a side note, is there a way to copy the points total from the query into a points field on the QUESTIONNAIRES Table, if so would it be easier to copy it over and then link it to the scheme table (using the score as the ID, in a simple lookup)?

 

[Andrew Fergus]

Now you have a table of scores and levels you can use a DLookUp function in your query to return the level. Is there a mathematical relationship between the score and the level? If so, there may be an easier way of doing this.

Andrew

 

[Age of madness]

nope, the only maths involved come from coverting the Q to the Q score, and then adding these together to get the points total. the level is fixed (anyone who scores 200 would get level 5, no variation to that rule)

Looking through i think the Dlookup needed would be along the lines of:

=Dlookup("Level","SCHEME1","Points")

Level being the field it is looking up, in the table Scheme 1, and where the record im referencing is Points? have i got that right?

Daniel

 

[Andrew Fergus]

Hi Daniel

Almost. You need to reference the score (in the table) to the points (in the query), and the syntax requires square brackets like this :

Grade : DLookUp("[Level]","SCHEME1","[Score] = " & [Points])

HTH, Andrew