If "IIF" is "IF" can you link tables by criteria?

[77sbetter]

Hello, I am struggling with transitioning from Excel to Access for its intended purpose. I am a student, who is barely appreciating Excel and all of its capabilities, and now required to utilize Access in its most basic form. For whatever reason, my brain does not function well with basic anything, hence my struggle and frustration. Unfortunately, now yours as well, so here goes:

Is it possible to, upon a calculated field's result return a text value based on a range of numbers within a certain range, from another table and link those tables with "enforce referential integrity" for a one-to-many relationship?

Example: TABLE1: 1000-940=A, 939-900=A-, 899-870=B+, etc. This is one table with the range of numbers in one field, and the text letter in another field, and the primary key auto-numbered.
TABLE2: CourseID, Week1, 2, 3, 4, TotalPoints, and Grade.
TotalPoints is a calculated field of week totals 1-4 and its result determines the Grade from TABLE1.

My question is if the "Grade" field can return the correct text value based on the points calculated in table2, then referencing table1 to find its range = A, B, C-, etc.?

IIF TotalPoints from TABLE2 BETWEEN 900 AND 939 = >A<#;

I know this is simplistic, and probably more work than is necessary, but is it possible?

Thank you!

 

[G37Sam]

One way of doing this would be having a table named Grades with three fields that looks something like this:

LowerLimit  UpperLimit  LetterGrade
940         1000        A
900         939         A-

Then using dlookup to retrieve the letter grade:

dlookup("LetterGrade","Grades","LowerLimit<" & Grade & " AND UpperLimit>" & Grade)

 

[77sbetter]

Thank you so much! I am slowly compiling the functions that are pertinent to my needs in hopes of achieving and utilizing Access to its capabilities. Sheesh, I hope I have enough years left.