Make Query From Three Fields and make a New Field (1 Viewer)

[rpraveen6r]

Hi Sir...

I am new to MS Access. I am entrusted with a project in my office. The project table has three fields 'Theory Total', 'Practical Total' and 'Grand Total'. My requirement is query should look into these fields and say that if 'Theory Total' is less than 60% marks then it should say 'Failed in Theory'. Same way to 'Practical Total'. If any body score more than 80% in 'Grand Total' then it should say 'Pass' otherwise 'Fail'

___________________________________________________________
Theory Total Practical Total Grand Total Result
--------------------------------------------------------------------
45 Failed in Theory
72 67 Failed in Practic
72 85 81 Pass

 

[JHB]

I think you can use the IIf function, else you need UDF.
Here is a description of the IIf-function.
http://www.techonthenet.com/access/functions/advanced/iif.php

 

[arnelgp]

SELECT IIf([Theory Total]<60,Format([Theory Total],"@@@") & " Failed in Theory",IIf([Practical Total]<60,Format([Theory Total],"@@@") & " " & Format([Practical Total],"@@@") & " Failed in Practical",IIf([Grand Total]<80,Format([Theory Total],"@@@") & " " & Format([Practical Total],"@@@") & " " & Format([Grand Total],"@@@") & " Failed",Format([Theory Total],"@@@") & " " & Format([Practical Total],"@@@") & " " & Format([Grand Total],"@@@") & " Passed"))) AS [Total Result]
FROM tblTheoryTotal;