469,106 Members | 2,327 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

DLookup equivalent in SQL Server

I am migrating a student database from Access to SQL Server. In Access
I have a query that displays grade information (grades are calculated
on a 12-point scale). In the query I average the students' scores and
store it in a column called Avg. I look up and display the equivalent
grade letter using Access' DLookup function from a table called
GradeTable_tbl. Here is how it's built in Access:
Grade: DLookUp("[grade_letter]","GradeTable_tbl","[grade_num]= " &
Int([Avg]))

Here is the structure of the GradeTable_tbl:
grade_num grade_letter
0 F
1 F
2 D-
3 D
..
..
..
10 B+
11 A-
12 A
How would I do the same thing in SQL Server? I want my output to be
something like:
Student Score1 Score2 Score3 Avg Grade
Bob 12 10 8 10 B+
Nancy 12 11 11 11 A-
etc...
I appreciate your feedback!

-Paul
------
"You never know enough to know you don't know"

Jul 23 '05 #1
3 18193
On 16 Feb 2005 06:59:35 -0800, Paul wrote:
I am migrating a student database from Access to SQL Server. In Access
I have a query that displays grade information (grades are calculated
on a 12-point scale). In the query I average the students' scores and
store it in a column called Avg. I look up and display the equivalent
grade letter using Access' DLookup function from a table called
GradeTable_tbl. Here is how it's built in Access:
Grade: DLookUp("[grade_letter]","GradeTable_tbl","[grade_num]= " &
Int([Avg]))

Here is the structure of the GradeTable_tbl:
grade_num grade_letter
0 F
1 F
2 D-
3 D
.
.
.
10 B+
11 A-
12 A
How would I do the same thing in SQL Server? I want my output to be
something like:


Select S.Student, S.Score1, S.Score2, S.Score3, S.Avg,
G.Grade_Letter AS Grade
from Student AS S
inner join GradeTable_tbl AS G on S.Avg = G.grade_num
Jul 23 '05 #2
Perfect - thanks! I had tried doing this with a "where" clause. Does
this not work?

Thanks again for your input.

Jul 23 '05 #3
On 16 Feb 2005 17:19:19 -0800, Paul wrote:
Perfect - thanks! I had tried doing this with a "where" clause. Does
this not work?

Thanks again for your input.


A WHERE clause should work the same - it even generates the same execution
plan.

Select S.Student, S.Score1, S.Score2, S.Score3, S.Avg,
G.Grade_Letter AS Grade
from Student AS S, GradeTable_tbl AS G
where S.Avg = G.grade_num
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Davey | last post: by
8 posts views Thread by Christine Henderson | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.