change letters to numbers in access

[Guest]

I have created a simple grades database in Access. I need to calculate the
added value for each student: that is the difference between Minimum Target
Grade and Actual Exam Grade. These grades are letters (A,B,C,D,E,U). How can
I subtract A-C?
I can do it in Excel but I want to avoid spreadsheets for a whole series of
reasons.
Any solutions will be much appreciated.

Eamonn Power

 

[Guest]

Cool problem. How about the Asc function that give the ASCII value of a letter.

Debug.Print Asc("A") - Asc("C") = -2

One problem would be that A and a have different values. Therefore something
like this might work better:
Debug.Print Asc(UCase("A")) - Asc(UCase("c"))

If you don't like the negative number, then:
Debug.Print Abs(Asc(UCase("A")) - Asc(UCase("c")))

The real problem shows up with the U grade. What happened to good old Fs!

Another method would be to write a case statement in a function that would
convert the characters to numbers. Or create a table that has fields to look
up the values.

 

[John Vinson]

I have created a simple grades database in Access. I need to calculate the
added value for each student: that is the difference between Minimum Target
Grade and Actual Exam Grade. These grades are letters (A,B,C,D,E,U). How can
I subtract A-C?
I can do it in Excel but I want to avoid spreadsheets for a whole series of
reasons.
Any solutions will be much appreciated.

Eamonn Power

In addition to Jerry's suggestions, you may want to consider having a
small Grades table with two fields: LetterGrade and NumericGrade. E.g.

A 5.0
B 4.0
C 3.0
D 2.0
E 1.0
U 0.0

This table could be joined to your grades table (joining by letter
grade) and you could use the value in calculations.

It has the advantage that you could (if desired) go to a finer
division of grades, by adding records such as

A- 4.7
B+ 4.3

and so on.

John W. Vinson[MVP]

 

[Guest]

Thanks for the help but I don't think my skills are upt o what you're
suggesting.

Eamonn

 

[Guest]

John,

I got your answer to my initial query and have spent several hours trying to
get it to work. I am not an expert Access user so if you have the time could
you explain your solution a little more. I created the table you suggested
and joined it in a query. However it did not become dynamic when I ran the
query, i.e. there was no replacemnt of the letters with numbers. What would I
have to do? I appreciate any help you could give me.

Eamonn

 

[John Vinson]

John,

I got your answer to my initial query and have spent several hours trying to
get it to work. I am not an expert Access user so if you have the time could
you explain your solution a little more. I created the table you suggested
and joined it in a query. However it did not become dynamic when I ran the
query, i.e. there was no replacemnt of the letters with numbers. What would I
have to do? I appreciate any help you could give me.

I'm suggesting that you should NOT "replace" the letters with numbers.

Instead, use the Query to return the number corresponding to each
letter.

I don't know your table or fieldnames so I can't make very specific
questions - could you perhaps open the Query in SQL view and post the
SQL text here, and indicate just how you are trying to use it?

John W. Vinson[MVP]

 

[Guest]

John,
SELECT tblReport.ReportID, tblReport.Subject, tblReport.unitCode,
tblReport.StudentID, tblReport.examgradeconvert AS Expr1, tblReport.examyear,
tblReport.examterm, tblReport.lettergrade, tblStudent.StudentSurname,
tblStudent.StudentName, tblStudent.MTG
FROM tblStudent INNER JOIN (tblReport INNER JOIN tblconvertgrades ON
tblReport.lettergrade = tblconvertgrades.lettergrade) ON tblStudent.StudentID
= tblReport.StudentID;

The code from my last attempt is this. What I was try to do was, somehow, to
end up with number equivalents and then subtract them. I thought that by
running an appropriate query I would activate the exchange between the grades
and numbers.

SELECT tblStudent.StudentSurname, tblStudent.StudentName, tblStudent.MTG,
tblStudent.numericgrade, tblReport.lettergrade, tblReport.numericgrade
FROM tblStudent INNER JOIN (tblconvertgrades INNER JOIN tblReport ON
tblconvertgrades.lettergrade=tblReport.lettergrade) ON
tblStudent.StudentID=tblReport.StudentID;

This is another attempt to activate the exchange.

Many thanks

Eamonn

 

[John Vinson]

John,
SELECT tblReport.ReportID, tblReport.Subject, tblReport.unitCode,
tblReport.StudentID, tblReport.examgradeconvert AS Expr1, tblReport.examyear,
tblReport.examterm, tblReport.lettergrade, tblStudent.StudentSurname,
tblStudent.StudentName, tblStudent.MTG
FROM tblStudent INNER JOIN (tblReport INNER JOIN tblconvertgrades ON
tblReport.lettergrade = tblconvertgrades.lettergrade) ON tblStudent.StudentID
= tblReport.StudentID;

The code from my last attempt is this. What I was try to do was, somehow, to
end up with number equivalents and then subtract them. I thought that by
running an appropriate query I would activate the exchange between the grades
and numbers.

SELECT tblStudent.StudentSurname, tblStudent.StudentName, tblStudent.MTG,
tblStudent.numericgrade, tblReport.lettergrade, tblReport.numericgrade
FROM tblStudent INNER JOIN (tblconvertgrades INNER JOIN tblReport ON
tblconvertgrades.lettergrade=tblReport.lettergrade) ON
tblStudent.StudentID=tblReport.StudentID;

This is another attempt to activate the exchange.

Let me say it again:

You are taking the wrong approach.

You *DO NOT NEED* to "exchange* the fields. You *DO NOT NEED* to
replace the letter grade with the number grade.

Instead, you link to the number grade table, *and simply use the
number stored in that table* in your calculations.

The field tblReport.numericgrade *SHOULD NOT EXIST*. You don't need
it.

Try

SELECT tblReport.ReportID, tblReport.Subject, tblReport.unitCode,
tblReport.StudentID, tblConvertGrades.numericgrade,
tblReport.examyear, tblReport.examterm, tblReport.lettergrade,
tblStudent.StudentSurname, tblStudent.StudentName, tblStudent.MTG
FROM tblStudent INNER JOIN (tblReport INNER JOIN tblconvertgrades ON
tblReport.lettergrade = tblconvertgrades.lettergrade) ON
tblStudent.StudentID = tblReport.StudentID;


John W. Vinson[MVP]