none
setting up a calculated field? RRS feed

  • Question

  • I have three columns on a table that I need to get more ideas about how to handle

    Assessment     Exam    Total=(Assessment + Exam)    LetterGrade (A, C, D or F)    Comment

    LetterGrade/Comment will depend on what is in Total. How do I set up Total as a calculated field?  How do I set up the other two fields? Should I set it up from the front end on the tables? I am using visual basic.net.

    Thanks

    Thursday, October 19, 2017 5:49 PM

Answers

  • ... since this is not a calculated field, I will appreciate all help. 

    Hi,

    What do you mean by the LetterGrade not being a calculated field. It should be, because you are trying to calculate the letter grade equivalent based on the Total field.

    • Marked as answer by alobi Saturday, October 21, 2017 5:43 PM
    Saturday, October 21, 2017 5:24 PM
  • Hi,

    Did you do it this way?

    Iif([Total]>=90,"A",
       Iif([Total]>=80,"B",
           Iif([Total]>=70,"C",
               Iif([Total]>=60,"D",
                   Iif([Total]>=50,"E",
                      IIf([Total]<=0,"-","F"))))))
    Hope it helps...



    • Edited by .theDBguy Sunday, October 22, 2017 1:43 AM
    • Marked as answer by alobi Sunday, October 22, 2017 7:04 AM
    Sunday, October 22, 2017 1:42 AM

All replies

  • I have three columns on a table that I need to get more ideas about how to handle

    Assessment     Exam    Total=(Assessment + Exam)    LetterGrade (A, C, D or F)    Comment

    LetterGrade/Comment will depend on what is in Total. How do I set up Total as a calculated field?  

    Don't do it in a table but in your query.  Same for the Letter Grade.

    Build a little, test a little


    Thursday, October 19, 2017 5:54 PM
  • I am using visual basic.net.

    What are you using for the backend? If you're using Access 2010 or later, you can set up calculated fields at the table level.
    Thursday, October 19, 2017 8:53 PM
  • Hi alobi,

    If you just want get the result in your vb.net application, I would also suggest you do this work by your query instead of change the table design.

    If you just want to change to table design, as theDBguy said, you could add calculate field at table level.

    The expression for ToTal is like

    [Assessment]+[Exam]

    The expression for LetterGrade is like

    Iif([Total]>=90,"A",
    
          Iif([Total]>=80,"B",
    
              Iif([Total]>=70,"C",
    
                  Iif([Total]>=60,"D",
    
                      Iif([Total]>=50,"E","F")))))

    You could adjust these expressions for your need.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 7:24 AM
  • For the backend I am using Access 2013

    Thanks

    Friday, October 20, 2017 10:13 AM
  • I am able to set up the expression calculating Total  in the expression builder.  but am I have have problem how to go about pulling up  the wizard  for expression builder to set up LetterGrade part of it and enter the code below since this is not a calculated field, I will appreciate all help.

    Iif([Total]>=90,"A", Iif([Total]>=80,"B", Iif([Total]>=70,"C", Iif([Total]>=60,"D", Iif([Total]>=50,"E","F")))))

    thanks for your time


    Saturday, October 21, 2017 11:58 AM
  • ... since this is not a calculated field, I will appreciate all help. 

    Hi,

    What do you mean by the LetterGrade not being a calculated field. It should be, because you are trying to calculate the letter grade equivalent based on the Total field.

    • Marked as answer by alobi Saturday, October 21, 2017 5:43 PM
    Saturday, October 21, 2017 5:24 PM
  • When student does not take a particular subject Total will be zero, and letterGrade is "F", so I added IIf([Total]<=0, "-")))))) but I kept getting and error message that the expression is incomplete. I a sure It something I am doing wrong. Is there any other way to present this. so that lettergrade will not be "F" when a student is not required to take the Subject?. thank
    Saturday, October 21, 2017 10:31 PM
  • Hi,

    Did you do it this way?

    Iif([Total]>=90,"A",
       Iif([Total]>=80,"B",
           Iif([Total]>=70,"C",
               Iif([Total]>=60,"D",
                   Iif([Total]>=50,"E",
                      IIf([Total]<=0,"-","F"))))))
    Hope it helps...



    • Edited by .theDBguy Sunday, October 22, 2017 1:43 AM
    • Marked as answer by alobi Sunday, October 22, 2017 7:04 AM
    Sunday, October 22, 2017 1:42 AM
  • Thanks
    • Marked as answer by alobi Sunday, October 22, 2017 7:04 AM
    • Unmarked as answer by alobi Sunday, October 22, 2017 7:04 AM
    Sunday, October 22, 2017 7:04 AM
  • Hi,

    You're welcome! We're all happy to assist. Good luck with your project.

    Sunday, October 22, 2017 2:31 PM