TABLES
Figure 1 shows data for four tables. Imagine you are a teacher and need to calculate a final grade for each student in a class. The Studentz table has five records. It includes fields for an AutoNumber primary key called StudentID, student name, and two fields that will be calculated: Pct (which represents the student’s cumulative average for the class) and Grade (which represents the student’s final letter grade for the class). Normally it isn’t a good idea to store calculated fields, but these point-in-time values become part of a student record.
The Workz table has an AutoNumber primary key called WorkID, and it contains a record for each test, quiz, homework, and assignment each student must complete and the points each item is worth.
The Gradez table is used to lookup a grade from a range, such as anything above 90% being equal to an A. The StudentWork table is a cross-reference for Studentz and Workz with Long Integer foreign keys StudentID and WorkID. It contains a field for Points earned.
TOTALS QUERY
Create a query using the StudentWork table to add up the total points for each student. Here is the SQL:
SELECT StudentID
, Sum(Points) AS SumPoints
FROM StudentWork
GROUP BY StudentID;
Internally, the data type for Points is Currency because of its accuracy handling digits. That data type includes a currency indicator we don’t want, so set the Format property of the column to be Standard with one decimal place. Save the query as “qStudent_SumPoints.”
Create a new query using the qStudent_SumPoints query and Workz table as the data sources. Get StudentID and SumPoints from the qStudent_SumPoints query. Make a calculated field for the final percent using the expression [SumPoints]/Sum([PointsWork]), and group it by StudentID.
Now we have the logic needed to update the Pct for each student. But using this query won’t work in an Update query. Therefore, we must find another way to do it.
SELECT QUERY WITH DSUM
What we can use in an Update query is a category of functions known as domain aggregate functions. All of the domain aggregate functions have the same syntax: DFunction(“Expression”, “Domain” [,“Criteria”]), where DFunction is the name of the specific function, like DSum or DCount; Expression is a field or formula; Domain is a table or query name; and Criteria, which is optional, indicates how to limit the records. In this case, we’ll get a sum of points for each StudentID. Use a Select query to get the logic right and view what is calculated:
SELECT StudentID
, DSum("Points", "StudentWork", "StudentID=" & [StudentID])
/DSum("PointsWork", "Workz") AS PctCalc
FROM Studentz;
Save this query as “qStudent_Pct_DSum.” It gives us the same values as the totals query we made but couldn’t use.
UPDATE QUERY
Assuming you’re making these queries in Query Design, you can turn what you have into an Update query with this SQL:
UPDATE Studentz
SET Studentz.Pct =
DSum("Points", "StudentWork", "StudentID=" & [StudentID])
/DSum("PointsWork", "Workz");
Save this as “qUp_Student_1_Pct” and then run it. All five student records are updated. Two students will be shown to be failing because our query didn’t consider if all the work was turned in. One of the students is missing a homework assignment, and another hasn’t scheduled a make-up exam after missing the final. Neither of these students can have a grade yet.
Before addressing the issue with our query, we need to undo the changes we just made to these records. Create an Update query to reset Pct to contain no value. Here’s the SQL:
UPDATE Studentz
SET Pct = Null;
Save the query as “qUp_Student_reset_grades,” and run it to clear previous calculated values.
QUERY TO LIMIT RECORDS
Make a Select query to see students who have submitted the same number of works as the class requires:
SELECT StudentID
FROM Studentz
WHERE DCount("*", "StudentWork", "StudentID=" & [StudentID] )
=DCount("*", "Workz");
When viewing the data, you can see that only three of the five students have completed the course requirements. Save this as “qStudents_Done.”
MODIFY UPDATE QUERY
Now let’s limit the qUp_Student_1_Pct Update query to the students who have completed the requirements by changing the SQL to:
UPDATE Studentz
INNER JOIN qStudents_Done
ON Studentz.StudentID = qStudents_Done.StudentID
SET Studentz.Pct = DSum
("Points", "StudentWork", "StudentID=" & [Studentz].[StudentID])
/DSum("PointsWork", "Workz");
When you run it, only three records will be updated.
UPDATE ANOTHER FIELD
Now that we have a Pct for each student who has completed all the work for the class (and Null for those who haven’t), make an Update query that looks up a grade. Use the DLookup domain aggregate function to update Grade where the student’s Pct is greater than or equal to the low value for the grade range and less than the low value for the next range. Only records with something in the Pct field will be changed:
UPDATE Studentz
SET Studentz.Grade = DLookUp(
"Grade", "Gradez"
, "Lo <=" & [Pct] & " And NextLo > " & [Pct])
WHERE ( Studentz.Pct Is Not Null );
Save as “qUp_Student_2_Grade” and run it. Three records are updated with the final letter grade.
Download this month’s databases: SF1909_UndoingUpdates
SF SAYS
To undo updates, make a query to reset the changed fields to Null, then modify the logic used and run the Update queries again.
September 2019
