• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Query Formula - Awarding Grades

Well, my question is an important one but it should be easy for some to answer with advanced experience of MS Access:

The general aim of my case study is to produce a database which manages the re-marking of examination scripts.

I have two tables; One with candidate and the other with subject information (which includes grade boundaries).

With a query, I would like to link these tables so that I can produce a number of results that the case study requires of me.

---

What formula would you suggest I use if I wanted a field to produce the "grade" (e.g. A,B,C) using the information of "mark" and "maxmark"?

Thank you for reading my question. :-)
0
Kouga
Asked:
Kouga
1 Solution
 
shanesuebsahakarnCommented:
What is the structure of your tables ?
0
 
njelgerCommented:
Fieldnames would be nice at least. But Just make a query and add tyge to tables and make a link between the equal fields. (pull the id field over to the eqiuvalent in the other table) Then add your selected  output fields.(doubleclick)

// j
0
 
HummusxCommented:
SELECT Candidate.Name, Subject.Name, IIF(Subject.Mark > 89, 'A', IIF(Subject.Mark > 79, 'B', IIF(Subject.Mark > 69, 'C', IIF(Subject.Mark > 59, 'D', 'F') AS Grade FROM Candidate JOIN Subject on Candidate.ID = Subject.CandidateID


This will return results like:

Joe  Math     A
Joe  English  B
Joe  History  A


Assumptions in the query above are:

Candidate is the name of the candidate table
Subject is the name of the subject table

Name, ID is a field in Candidate table
Name, Mark, CandidateID are fields in Subject table

Tables are related by the ID fields


Of course you can fill in your own table/field names instead.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
KougaAuthor Commented:
The field names are:

candidate table: TBL1-CANDIDATE DETAILS

candidate_no
candidate_surname
candidate_forename
centre_no
sub_ref_no
original_mark
remark_mark
request
date

subject table: TBL2-SUBJECT DETAILS

sub_ref_no
sub_name
maxmark
a
b
c
d
e

----

I want to produce a double IF statement to calculate the students grade in a query. Is this is enough information for you to help? :)
0
 
annineCommented:
For show all cases:
SELECT candidate.candidate, subjectinfo.subject
FROM candidate, subjectinfo;

For number of cases:
SELECT Count(*)
FROM candidate, subjectinfo;

Hope can help..
0
 
KougaAuthor Commented:
Well, I fixed it myself. I worked at the 'percentage' first and used the statement:

new grade: IIf([percentage]>=[a],"A",IIf([percentage]>=[b],"B",IIf([percentage]>=[c],"C",IIf([percentage]>=[d],"D",IIf([percentage]>=[e],"E",IIf([percentage]<[e],"U"))))))

0
 
HummusxCommented:
Isn't that basically exactly what I posted?
0
 
nexusnationCommented:
Hi Kouga,
This question has been abandoned and needs to be finalized (103 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to Hummusx
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now