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

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Eamonn" wrote:

> 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

On Wed, 1 Feb 2006 06:44:28 -0800, Eamonn
<(E-Mail Removed)> wrote:

>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]

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

Eamonn

"Jerry Whittle" wrote:

> 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.
> --
> Jerry Whittle
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Eamonn" wrote:
>
> > 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

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" wrote:

> On Wed, 1 Feb 2006 06:44:28 -0800, Eamonn
> <(E-Mail Removed)> wrote:
>
> >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]
>

On Mon, 6 Feb 2006 01:40:29 -0800, Eamonn
<(E-Mail Removed)> wrote:

>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]

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" wrote:

> On Mon, 6 Feb 2006 01:40:29 -0800, Eamonn
> <(E-Mail Removed)> wrote:
>
> >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]
>

On Wed, 8 Feb 2006 05:21:28 -0800, Eamonn
<(E-Mail Removed)> wrote:

>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]