Jump to content
  • 0
vanderLeest

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

Question

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?

Update to Max5.png

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...