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.
Steps in the Process:
-
Create a Select query to select the tables and fields needed to select what you want to update.
-
Set criteria.
-
Change the query to an Update Query. A new row appears in the grid, Update To.
-
Enter the new value.
-
View the datasheet.
-
Run the query. You will get a warning about how many records will be updated.
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",
(IIf([Score]>69.5,"D","F")))))))))
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.