Using IF Statements?

[Sabotage1945]

Hi everyone! I'm new to SQL and Access, and I would like to ask your help:

I've been asked to create some way (SQL script with formula) for me to use in order to 'read' and 'place' an equivalant numerical number to an alphabetical list. Here is an example:

I'm creating a student report list. The students have marks listed as 'A+', 'A', 'A-', 'B+', 'B', etc. I would like a quick an easy way of converting these in a NEW column in a numeric format. ie: 'A+' = '10' and down to 'F' = '0'

At present I'm having to do a find and replace in Excel - but on large files this takes too long. Can someone recomend a good way to do this?

Here is something I've tried in Excel that has worked - to a point, but the results are sometimes not what I want. I'm guessing there is a way to incorporate this into an ACCESS SQL:

=IF(E3="","",VLOOKUP(E3,{"A+",10;"A",9;"A-",8;"B+",7;"B",6;"C+",5;"C",4;"D+",3;"D+",2;"E",1;"F",0;"INC",0;"ABS",0},2))

Version 2:
=LOOKUP(E2,{"A+","A","A-","B+","B","C+","C","D+","D","E","F","INC","ABS"},{"10","9","8","7","6","5","4","3","2","1","0","0","0"})

Both have the same result in Excel where E2 has "A+", but the result is incorectly listed as "8".

Is there a better way to do a similar query in Access?

Thank you!
Sab.

 

[M8KWR]

The way i would do it, not saying its the fastest, but i know it works. Is to use an update query.

Do the following:-

Create a new table (i've called it New_Grade), with two columns (Grade and Value), one the Grade i.e A+ etc, and in the other column put the Grade which you need to change it to.

code:

UPDATE New_Grade INNER JOIN Old_Grade ON New_Grade.Grade = Old_Grade.Grade SET New_Grade.Grade = [new_grade]![value];

You would have to replace where it say "old_grade" with the name of your table.

Hope this helps.

 

[Sabotage1945]

Thanks for your reply M8KWR,

I've done as you suggested, but for some reason its not working out for me. I've even named my Tables in the same format as you have but still nothing...

When I copy your code into my Queries window and run it, I first get a dialog stating 'Enter Parameter Value':

"new_grade!value", OK, Cancel

So I've typed "10" and 'OK'

I get another dialog

"old_grade!value", OK, Cancel

So I type "A+" and click OK

There is a new dialog which states it is updating the 62939 records I have, and then nothing. So I checked the "old_grade" table, and nothing has been changed, and I check the "new_grade" and I notice that where "A+" had originally been placed is now a "10". So it reads "10" on one column, and "10" in another.

Unfortunately it did not come up with an equivalent numeric value to the data in the "old_grade" table.

Thanks again and if you or anyone can help me out - I'm still in dire need

Cheers,
Sab.

 

[dcx693]

If this is a one-off thing, then I'd do the update query approach already suggested. Just looking quickly at the last post, perhaps [new_grade]![value]; needs to be new_grade.value; or something like that.

Otherwise, I'd do a variation. Create a new table with the letter grade and the numeric value, join it to your existing table on the letter grade field and now you can write a query that pulls the numeric value off your new table.

 

[Sabotage1945]

I appreciate the suggestion dcx693,

Unfortunately this will not be a 'one-off thing'. I've got hundreds of these to do, and many to keep up from semester-to-semester and year-to-year.

Oh I see what you're suggesting... I'll try to come up with a SQL code to do that. I'm only a newb so it may take some trial and error with this kind of code.

Much appreciated,
Sab.

 

[dcx693]

There's no need for manual SQL coding. Just go create yourself your new grade table and create a new query in design view.