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"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|||Perfect - thanks! I had tried doing this with a "where" clause. Does
this not work?
Thanks again for your input.|||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
