Remarks with number

[syedadnan]

Regards,

I want to get remarks as like this i have a query which is showing the total of different fields suppose the total is 77 and i want to give a crietria field by name of net level which shall work like this ..

if the number is from 0 to 33 then then the result should be C-1
if from 33 to 40 then C-2
if from 41 to 50 then C-3
if from 51 to 60 then B-1
if from 61 to 70 then B-2
if from 71 to 80 then B-3
if from 81 to 90 then A-2
if from 91 to 100 the A-1

 

[pr2-eugin]

You mean something like Lookup a value in Range?

 

[syedadnan]

You mean something like Lookup a value in Range?

Yeah i something like this but not knowing how to write formula or sql for this could you plz guide

 

[pr2-eugin]

If you are looking for a very simple answer, and avoid all the problems that might occur as pointed out by Allen Browne;

If users are allowed to create brackets with both their beginning and ending points, they will almost certainly create brackets that overlap or have gaps.

Then all you have to do is create a table, tbl_gradePoints with three (four if you have a primary key) columns.

gradeID        gradeStart    gradeEnd    actualGrade
1                 0                33        C-1
2                34                40        C-2
3                41                50        C-3
4                51                60        B-1
5                61                70        B-2
6                71                80        B-3
7                81                90        A-2
8                91                100       A-1

Then to get the grade, you just use them in the Query.

SELECT yourTableName.studentID, yourTableName.studentName, tbl_gradePoints.actualGrade
FROM yourTableName.marks Between tbl_gradePoints.gradeStart And tbl_gradePoints.gradeEnd;

 

[syedadnan]

If you are looking for a very simple answer, and avoid all the problems that might occur as pointed out by Allen Browne;

Then all you have to do is create a table, tbl_gradePoints with three (four if you have a primary key) columns.

gradeID        gradeStart    gradeEnd    actualGrade
1                 0                33        C-1
2                34                40        C-2
3                41                50        C-3
4                51                60        B-1
5                61                70        B-2
6                71                80        B-3
7                81                90        A-2
8                91                100       A-1

Then to get the grade, you just use them in the Query.

SELECT yourTableName.studentID, yourTableName.studentName, tbl_gradePoints.actualGrade
FROM yourTableName.marks Between tbl_gradePoints.gradeStart And tbl_gradePoints.gradeEnd;

Not finding the soultion yet..

Just guide me like if i want to get result suppose if figure is between 0-33 then result should be C-3
then if 34-40 then result should be C-2 just iif criteria ?? may be in query doesnt work ?

 

[pr2-eugin]

Just guide me like if i want to get result suppose if figure is between 0-33 then result should be C-3

I just did ! :banghead:

I can only lead the horse to water, I cannot make it drink !

 

[Ari]

Hi

One other way to lead a horse to the source.

Copy and paste the function below to a global module:

Public Function fncGrade(intNum%) As String
Select Case intNum
    Case 0 To 32: fncGrade = "C-1"
    Case 33 To 40: fncGrade = "C-2"
    Case 41 To 50: fncGrade = "C-3"
    Case 51 To 60: fncGrade = "B-1"
    Case 61 To 70: fncGrade = "B-2"
    Case 71 To 80: fncGrade = "B-3"
    Case 81 To 90: fncGrade = "A-2"
    Case 91 To 100: fncGrade = "A-1"
    Case Else:: fncGrade = "X-X"
End Select
End Function

Create a Virtual field in the query by calling the function.

ActualGrade: fncGrade([NameFieldQuery])

 

[CazB]

But given what you said in your OP, syedadnan, then 0-33 should be C-1, not C-3? And Paul's example would find that for you?

 

[syedadnan]

The horse is drinking water now .... Marvellous ARI the work done