473,327 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 18891
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Davey | last post by:
Which is typically faster - a Java server application or a C++ server application?
3
by: DFS | last post by:
FYI, Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE numbers each time it's called. Anyone know why?
8
by: Christine Henderson | last post by:
I have a problem using the above function in the following simplified circumstance: In the lookup table called "Klms Travelled" I have 3 fields, eg: Receiver Name Receiver Suburb ...
0
by: musman | last post by:
hey all, I have tried to use the select statement instead DLookUp function as i have sql server at my backend and access as my front end. But neither DLookUp function is working nor select state...
9
by: | last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the...
2
by: RoadrunnerII | last post by:
Hi All I am new to this forum and still learning MS Access. Hoping this is the right place to ask If not please let me know! Looking for some help with the SQL statements in Access 2003 with the...
2
by: Denise | last post by:
Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a...
3
by: Kunal Desale | last post by:
Hello, I am migrating access queries to SQL Server 2005 Queries. My Access Query Is: SELECT qtrade.intordreftrim AS Expr1, qtrade.extordreftrim AS Expr2, qtrade.intinvreftrim AS Expr3,...
15
by: rleepac | last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue This is a table used to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.