Calculated field to lookup its value based on another field (1 Viewer)

[Lancer]

Is there a way that I can make a field in a table (I don't mind using a query if you can show me how) which is a calculated field and gets its value by performing a look up (from a another table list) based on another numeric field for that person?

e.g. In school grades, the student might get these marks...

-1 - Not submitted (F)
0 - Not Achieved (C)
1 - Achieved (B)
2 - Merit (A)
3 - Excellence (A+)

I already have their marks numerically (let's call the field "science_score" in my "marking" table) and I want to make another field for them which actually says their grade using the plain english version format (so where science_score is "2", this field should become "Merit (A)".

Better yet, I ultimately would like to also have the sentence written as "<student name> scored a <grade> in <his/her> science exam" with name, gender etc being utilised. So, it might look like this: "Sarah scored a Merit (A) in her science exam"

Can someone please show me how? I'm getting all manner of mismatch trying to make the lookups work to tables / queries etc. I would prefer to have the output feed to a Table which can go to a Form so that I can see the processed (final) sentences in front of me while I fill in other various comments to other fields.

I am normally used to using Excel for this (lots of long formulae based on multiple cells) but new to database which I hear is more efficient. The RTFM I've been attempting thus far has led me in circles and I'm now running short of time.

 

[Beetle]

Create a small table to store the descriptive values for the grades. This table would have two columns. Let's call them, for example, DescriptionID and GradeDescription;

tblGradeDescriptions
***************
DescriptionID (Number - Primary Key)
GradeDescription (Text)

Now create a query that joins this table to your "marking" table by creating a relationship between the science_grade field in the "marking" table and the DescriptionID field in tblGradeDescriptions. Add whatever fields you need from the marking table, plus the GradeDescription field from tblGradeDescriptions.

I ultimately would like to also have the sentence written as "<student name> scored a <grade> in <his/her> science exam"

This would also be done using a calculated field in a query or in a control (like a text Box) on a form. Basically you would be concatenating field values into a string. It would look something like;

[StudentName] & " scored a " & [GradeDescription] & " on this test."

If [StudentName] did not exist in the record source (i.e. in your markings table you would likely only have StudentID, not StudentName) you can retrieve it with DLookup. That would look more like;

DLookup("[StudentName], "[tblStudents]", "[StudentID]=" & [StudentID]) & " scored a " & [GradeDescription] & " on this test."

 

[Lancer]

Thanks for your input, though the method you described doesn't fully work for me

The science_grade is actually a few numbers added together. When I try to link science_grade to the lookup table....
I get the error, "Calculated columns cannot be used in system relationships".

What I have done in the meantime is copy-and-paste the whole calculated field (science_grade) as a new field altogether. I then changed this using "Lookup Wizard" so that it showed the english versions of the comments.

This method works, but hardly seem efficient; the calculated grade score is broken in the process so if I update the scores at any point, the data is flawed.

Dlookup seems promising though I'm still getting my head around how it might work (science_grade is only a simplified version).

 

[bob fitz]

Can you attach a copy of the db

 

[Lancer]

bob fitz - sorry, but they are school records, and I would have to rewrite all the names, marks etc in order to not breach professional confidentiality. Besides this, I'd then have to talk everyone through a very detailed "how school records are marked" process and I'm trying to keep the question simple. (The marks for a single assignment contain 12 separate marks and the comments along side them and this end term report cumulates multiple assignments)

Okay, I've started looking into Dlookup and it is along the lines of what I'm wanting. However, when I was using a spreadsheet is was easy to make lookup lists that contained multiple variables e.g:
value1: [name] is not using [his/her] revision time well.
value2: [name] could make more use of the class challenges [he/she] is attempting.
Value3: I am pleased that [name] always ties [his/her] hardest in all tasks.

...I can Dlookup to the values on a short reference list, though I'm getting unstuck on how to make those lists also include linked variables (fields) such as [name] and [his/her] depending on the person.

Edit:
I am also looking into the IIF function, which can be used in Form text tables. At this point it's not injecting data into any of the tables, but it is helping me make more complex summative statements.

simplified example:
IIF([sciencegrade]=0,[firstname]&" is not revising","I am pleased that "&[firstname]&" makes good use of "&[hisher]&" revision time")

I'm assuming the IIF can be nested so the last comment could be another IIF to show revision some versus all of the time.

This is very close to what I have been using with the IF statement in spreadsheets. My database workflow needs to improve no doubt, though I think this may be enough to help me kick start the automated comments. I'll work on these tonight, aiming to leave going over them (proofing and adding personal touches tomorrow when they are finally due for submission)