Refer to each line of a subform with a query

[chacenger]

I have a subform that contains information about different assignments and grades. The parent form represents a person. A person has multiple assignments and each assignment is assigned to more than just one person. My issue is that I need to rank each person on the grade they received for that assignment. For example, Assignment 1 is assigned to Person A, B, and C. If the grade of Person A on that assignment is 85%, Person B got 70%, and Person C got 90% then I need to determine the rankings (From highest mark to lowest: C -> A -> B) and update the Assignment 1 row of the currently loaded Person form. I know how to determine the ranks (which I thankfully found on this forum) however the problem I find is that when trying to run an update query to reference all of the assignments in a person's subform (to set the ranks of all of the assignments at once), the query to do so only references the very first row of the subform.

My ultimate question is how can I reference each row of a subform in a query. Currently it just sees the ID of the first entry in the subform and uses that for each subsequent row. This means that if a person got the highest grade on the first assignment in the subfrom, all of the assignment ranks below in the list for that person are also updated to '1st'.

Any help would be much appreciated.

[ItsMe]

Perhaps thinking of your objective as using tables and queries will shed some light on how to proceed. Right now you keep mentioning subform. You also mention an update query. Are you going to store the rankings in a table? Is this the objective?

Can you explain you objective using query and table objects only? A subform is only going to display results and if you still need to do calculations it would be beneficial to focus on get the calcs and then figure out how to display the calcs

[chacenger]

Thanks for the reply. The Person_Assignment table holds information of a person's assignment. The personID and assignmentID are foreign keys. This Person_Assignment table has a field called rank. Rank is meant to be updated to 1st, 2nd, 3rd... 30th etc. So my goal is to update this table to reflect the ranks for all of the different assignments.

[ItsMe]

If it is a simple calculation that needs to be done you can use a single UPDATE query to update the ranking field. You can build the action query using the query builder. Once it is saved you can use VBA to call the object. currentdb.execute "QueryName". You could also use the same command to execute an SQL string.

You can change the subform's recordset to adjust which records are displayed. You can adjust the recordsource to include variables in unbound comboboxes, unbound textboxes etc.

If the calculations are complex, like with rankings, you may need to use DAO to get the variables from the table and then use custom functions to do the math or custom functions to work with saved query objects. Saved query objects could assign values to variables that could then be used in subsequent calculations within a given formula