Dlookup equivalent

Question

Post reply

Dlookup equivalent

Paul-209278

Old Hand

Points: 314
More actions
February 16, 2005 at 8:03 am

#67057
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!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You could pretty much use the same report.

You have 2 ways of doing this

1 - let access do the work (as it is best pratice)

just put a textbox and put a sum as datasource like this :

=(Score1 + Score2 + Score3)/3

and on the detail format, put a select case on Avg

case 0 txtGrade = "F"

case 1 txtGrage = "F+"

2 - do it in sql server

Select score1, score2, score3, (score1 + score2 + score3)/3 as Avg,

CASE (score1 + score2 + score3)/3

WHEN 0 then 'F'

WHEN 1 ThEN 'F'

When 2 then 'D-'

...

end as Grade

from MyTable

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

Since you are storing the average in a column, you can join on the grade lookup table:

select Student, score1, score2, score3, [Avg], grade_letter as Grade from scores join GradeTable_tbl on [Avg] = grade_num

This would also be the preferred way to do it in Access as well. I would avoid doing lots of Dlookup calls unless you are just looking up one thing. For example, you may have a control table with the company name. You could use dlookup to display the company name on reports or forms.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Paul-209278 Old Hand Points: 314 More actions · Answer 3

Thanks for the reply. I guess I wasn't totally clear on my original post - I'm not actually storing the average in the table. It is just calculated in the query. In Access I can then use the alias for average in the DLookup function. Your method does work, I just need to recalculate the average again within the JOIN statement (unless someone knows how I can reuse the average that is calculated within the SELECT statement in the JOIN). Here's the code that works (thanks to Ross Presser via comp.databases.ms-sqlserver):

Select S.Student, S.Score1, S.Score2, S.Score3, (S.Score1 + S.Score2 + S.Score3)/3 AS Average,

G.Grade_Letter AS Grade

from Student AS S

inner join GradeTable_tbl AS G on (S.Score1 + S.Score2 + S.Score3)/3 = G.grade_num

Paul