Task to update a field value in one table with the max value of a field in a subsidiary table

[vanderLeest]

I want to update the SA_Last_MS field in the table T_PA_SubAwards with the Max value of the field PeriodNo in the subsidiary T_PA_Milestones table. The two tables are linked via the fields SA_RecNo and MS_SA_ID.

The task pictured runs but gives a value of 2 for all SubAwards with Milestones, and that is correct for most of them, although two SubAwards have 4 and 5 milestones, and these are erroneously set to 2 too.

Can someone tell me what is going on?

[vanderLeest]

The issue resides within an incorrect table name in the join statement in both select queries:
The join is pictured with the (alias name of the) T_PA_SubAwards_1 table, it should be with (the original) T_PA_SubAwards.

To me this is counter-intuitive (as the line above 'defines' T_PA_SubAwards as T_PA_SubAwards_1),  that said, somehow it makes more sense too.  ;-)

The pictured task will update the SA_Last_MS field in the table T_PA_SubAwards with the Max value of the field PeriodNo in the subsidiary T_PA_Milestones table, but only if that MAx value is different than the current value, or if SA_Last_MS is still blank.