customize a function in a form.

[SashaSandy]

Hi!!
I have a query that i would like to make it as custom as possible.
It grades students according to marks achieved, however grading systems may vary in different institutions or so.
I know there may be two approaches in tackling this like using a Table or parameter queries but i seem to end up in the same mess either way.
i use the following function to show grade:

Grade: IIf([Marks]>=80,"A",IIf([Marks]>=75,"A-",IIf([Marks]>=70,"B+",IIf([Marks]>=65,"B",IIf([Marks]>=60,"B-",IIf([Marks]>=55,"C+",IIf([Marks]>=50,"C",IIf([Marks]>=45,"C-",IIf([Marks]>=40,"D+",IIf([Marks]>=35,"D",IIf([Marks]>=30,"D-",IIf([Marks]<30,"E"))))))))))))

What i want to achieve is to alter the marks and grade as per user requirement in a form.


can this be possible if so how?

[pbaldy]

I would likely use a table with 3 main fields; one for the grade and two for the upper and lower limits for that grade. You can then have a form for the user to maintain the values. You can get the grade via any number of methods, including for this example a non-equi join.

[SashaSandy]

I would likely use a table with 3 main fields; one for the grade and two for the upper and lower limits for that grade. You can then have a form for the user to maintain the values. You can get the grade via any number of methods, including for this example a non-equi join.

thank you for your rep, the thing is those "many number of methods" am finding it veery hard to tackle. let me make it simpler to understand.

this is what i want to achieve.
I have a table of marks of student, a query that calculates totals and a form that displays results.
on the query, i have an if statement.

Code:

comment:IIf([Marks]>=80,"good",IIf([Marks]>=50,"fair",IIf([Marks]>=40,"Work harder")))
So i want to be able to make the whole syntax available for user to edit without
actually doing it in vba.
is this possible in vba? i would really appreciate your help.
i have attached an image that will help you understand exactly what i mean.

[SashaSandy]

i hope this image explains further.

[pbaldy]

I understand what you want. The structure you've chosen is harder to work with than the one I recommended. With mine, it's a relatively simple process of finding the value where the mark is >= the lower limit and <= the upper limit. It can be done with a DLookup() or in your query, a non-equi join.

[SashaSandy]

I understand what you want. The structure you've chosen is harder to work with than the one I recommended. With mine, it's a relatively simple process of finding the value where the mark is >= the lower limit and <= the upper limit. It can be done with a DLookup() or in your query, a non-equi join.

Thank you very much, i finally found a working solution. After i did a thorough search on the web i used the following SQL statement.

SELECT SM.Student_ID, SM.Students, SM.Marks, GD.GradeFROM tblStudentMarks AS SM, tblGradeDenormalized AS GD;

[pbaldy]

That's the non-equi join I mentioned.

[SashaSandy]

sorry, the full SQL statement for the query is:

SELECT SM.Student_ID, SM.Students, SM.Marks, GD.GradeFROM tblStudentMarks AS SM INNER JOIN tblGradeDenormalized AS GD ON (SM.Marks>=GD.MinMarks) AND (SM.Marks<=GD.MaxMarks);