Setting up a query

Question

I am using access database, I have the following columns in a table,

Cont_Assessment

Exam

Total

Letter_Grade

Comment

Total is a calculated value (Total=Cont_Assessment + Exam ). The value of Total determines the value in both Letter_Grade (A, B, C, D or F) and Comment (Excellent, Average, Pass, or Fail). The problem is, I am not sure how to set this up the query in Expression builder. I do not think that IIF () function is suitable here.  I will appreciate all help in setting this up. Thanks for your time

Answer 1

Hello,

Is this for a VB.NET project or within MS-Access?

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 2

You can use the IIF function but a more readable option would be to use the Switch function in your SQL statement:

https://www.techonthenet.com/access/functions/advanced/switch.php

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

It is for a vb.net project

Answer 4

Thanks for the link, I will check out get back to the forum

Answer 5

If the data is to be place say into a DataTable (or a DataTable in a DataSet) you can create a calculated column via a DataColumn.Expression (which is read-only).

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 6

Hi alobi,

Since this forum is discussing and asking questions about the Visual Basic programming language, IDE, libraries, samples, and tool, and your issue is more related to Access for developer, I will move this thread to the more related forum.

Thanks for your understanding and support.

Best Regards,

Cherry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" 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 [e-mail].

Answer 7

I am looking at the link Provided by Paul P clement IV, the switch function. I will get back to the forum with my findings. Or do you think that the Datatable tool is a better option? Thanks

Answer 8

 Thanks

Answer 9

Hello,

Have you resolved the issue?

If your issue persists, could you please share your expected query result?

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" 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 [e-mail].

Answer 10

The value of Total determines the value in both Letter_Grade (A, B, C, D or F) and Comment (Excellent, Average, Pass, or Fail).

I was out of the country at the time of your original post, so missed it.

If that statement is a true reflection of the functional dependencies, then the Letter_Grade and Comment columns should not exist within this table as they introduce redundancy and the consequent risk of update anomalies.  The Cont_Assessment and Exam columns should constitute a composite foreign key referencing  a table in which they are a candidate key, and which has Letter_Grade and Comment columns columns determined by the key.  In this referenced table the Letter_Grade and Comment columns should each be a foreign key referencing the primary keys of Letter_Grades and Comments tables respectively.

By joining your current table to the referenced table on the Cont_Assessment and Exam columns in a query the Letter_Grade and Comment values determined by Cont_Assessment and Exam can then be returned in the query, eliminating the redundancy which your current table exhibits.  A form for data entry into the current table can be based on such a query.  In the form the  controls bound to the Cont_Assessment and Exam columns should have their Locked property set to True (Yes) and their Enabled property to False (No) to make them read-only.

---

Ken Sheridan, Stafford, England

Answer 11

Awesome. I will go rearrange things and get back to the forum with update and latest arrangement for more direction Thanks all.