DLookup equivalent in SQL Server

[Paul]

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"

[Ross Presser]

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

[Paul]

Perfect - thanks! I had tried doing this with a "where" clause. Does
this not work?

Thanks again for your input.

[Ross Presser]

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