Dlookup using nearest value (vlookup true)

[jameshopper]

Hi,
I have worked on and searched long and hard to come up with a solution to the vlookup(true) Excel function in Access. May of the solutions I have found meant using several queries/subqueries to get to the right results. Luckily, I stumbled across this while looking through the built in functions and thought it would be worth a go. Blow me, it worked. I don't think this easy solution has yet been posted. If it has, I apologize.

Table 1 (lookup table)
Score Band
0 Does not meet
0.97 Meets
0.99 Exceeds
0.975 Minimum Performance

Table 2 (actual data table)
Score Name
0.98 James
0.96 Fred
0.99 Corinne
0.996 Thomas

SQL Code
SELECT Table2.Score, Table2.Name, DMax("[Score]","Table1","[Score] <=" & [Score]) AS Score_Band, DLookUp("[Band]","Table1","[Score] = " & DMax("[Score]","Table1","[Score] <=" & [Score])) AS Band_Description
FROM Table2;


Gave results:
Score Name Score_Band Band_Description
0.98 James 0.975 Middle
0.96 Fred 0 DNM
0.99 Corinne 0.99 Exceeds
0.996 Thomas 0.99 Exceeds

Granted, It's only a small data set to test, but I think it seems to do the trick. Let me know if there are any obvious errors.

James

 

[jameshopper]

I changed one of the bands descriptions during writing this, which is why one says middle and the other says minimum performance.

James