Jan's Working with Databases:

Update Query

Home > Jan's CompLit 101 > Working with Databases > Appendix

Example: Update Query

Problem: I need to convert number grades to letter grades.

Solution: Create a set of update queries, one for each letter grade.

In the LanguageArtsClasses database there is a set of update queries that assign a letter grade to scores. Let's look at the query Assign A to >=94.

Query Design View: Update QuerySteps in the Process:

The example query uses only one table, Scores, and only two fields from that table, LetterGrade and Score. The update query looks at the value in the Score field. If it meets the criteria >=94,  then the LetterGrade field is updated to A, the value in the row Update To. Similar queries have criteria for other grade ranges and assign the correct letter grade for each one.

The advantage of using update queries to assign letter grades is that they won't make a mistake! And, it avoids a lot of data entry. I really liked that part!

Of course with Access 2010 you can now have a calculated field in the table. The table itself can calculate the grade, but only if you can write a good nested IIF statements like:

IIf([Score] Is Null,Null,(IIf([Score]>93.5,"A",(IIf([Score]>89.5,"B",(IIf([Score]>79.5,"C",

Icon: Experiment Experiment: In the LanguageArtsClasses database, enter some scores in the Scores table for A/T ID 87 and 89, the partial records that were added the example Append query. Run the various update queries to assign letter grades to those scores.

Home > Jan's CompLit 101 > Working with Databases > Appendix

Last updated: September 17, 2012