Calculating GPAs in Access

  • Thread starter Thread starter Stumped...again
  • Start date Start date
S

Stumped...again

I have a basic understanding of Access, but have never needed to use it to do
anything with calculations... that's what Excel has done for me.

I currently have an Excel spreadsheet that will (for only one student at a
time) perform gpa calculations, convert a letter grade into a numeric value,
and give me an overall gpa. The only problem with it is that I need to do it
on a much larger scale. I'm now working with graduate programs that have
hundreds of students in each.

My goal is to take my Access database of students and their letter grades
and do the same thing my Excel spreadsheet does for me, but on a larger scale.

Is this possible? Do I need to be more specific about what I want to do?

Any and all help will be appreciated. Thanks in advance for your patience
with me :-)
 
Stumped...again said:
My goal is to take my Access database of students and their letter grades
and do the same thing my Excel spreadsheet does for me, but on a larger
scale.

Is this possible?
Click to expand...

I would have thought so yes, but if it works in Excel then why not carry on
using it? Access isn't a "beefed up" Excel, they're two different animals.
Do I need to be more specific about what I want to do?
Click to expand...

Yes. What calcs are in your spreadsheet?

Keith.
www.keithwilby.com
 
It certainly can be done, but your question doesn't contain enough detail to
provide you a working answer. The questions I would have are:
Are there more than one record for a student used in the calculation?
What is you current formula for the Excel calculation?
 
Sorry about that.

There is only one record per student.

The calculation I use is:
=IF(E11="A", C11*4, IF(E11="B", C11*3, IF(E11="C", C11*2, IF(E11="D", C11*1,
IF(E11="F", 0, "")))))
C11 shows how many credit hours each course is worth.

Do you require additional information?
 
That looks like a spreadsheet formula, not a relational database expression.

What does using MS Access let you do that you cannot already do in Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Use a translation table with these fields --
Grade Multiplier
A 4
B 3
C 2
D 1
F 0

In your query design view grid insert both tables. Click on Greek symbol
that looks like an ‘M’ on it’s side(∑).
Add fields –
Student Grade GPA: ([UrTable].[Credits]*[
tblTrans].[Multiplier])/Count([UrTable ].[Student])
UrTable tblTrans
Criteria –
UrTable. Grade
 
Thanks. I'll let you know how it works out.



KARL DEWEY said:
Use a translation table with these fields --
Grade Multiplier
A 4
B 3
C 2
D 1
F 0

In your query design view grid insert both tables. Click on Greek symbol
that looks like an ‘M’ on it’s side(∑).
Add fields –
Student Grade GPA: ([UrTable].[Credits]*[
tblTrans].[Multiplier])/Count([UrTable ].[Student])
UrTable tblTrans
Criteria –
UrTable. Grade

--
KARL DEWEY
Build a little - Test a little


Stumped...again said:
Sorry about that.

There is only one record per student.

The calculation I use is:
=IF(E11="A", C11*4, IF(E11="B", C11*3, IF(E11="C", C11*2, IF(E11="D", C11*1,
IF(E11="F", 0, "")))))
C11 shows how many credit hours each course is worth.

Do you require additional information?
Click to expand...
Click to expand...
 
I posted the spreadsheet formula because the other person asked for it. I
figured it would give him/her a better idea of what I'm trying to accomplish.

I'm trying to have all of my information in one location instead of going
back and forth between Access and Excel. It would also allow me to discover
and track trends and print reports for large numbers of students. Please
correct me if I'm wrong, but I don't believe Excel would work for that
purpose.
 
You can use a caclulated field in a query to do this, you would just need to
use fields in your table rather than cell references. The IIf in Acsess is
much like the If statement in Excel. But, rather than a bunch of nested
hard to read IIf statments, I prefer using the Switch function.
So something like this will work:

Gpa: [CreditHours] * Nz(Swith([LetterGrade] = "A", 4, [LetterGrade] = "B",
3, [LetterGrade] = "C", 2, [LetterGrade] = "D", 1, [LetterGrade] = "F", 0),
0)

The Nz function is included to avoid an error if the field LetterGrade is
something other than A, B, C, D, or F. In that case, the Switch returns a
Null which would throw an Invalid Use Of Null error, but the Nz converts a
Null to 0 to avoid the error.
 
Back
Top