Update issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Not sure if i can even do this in a query. I may need to write some code but
im throwing out there.

I have 2 table with a One to many relationships. They both have fields
named "completed" in them. I do not want the primary tables field to be
checked completed until it's many relationships in the secondary table have
been checked completed. Is there a query you can write that will do this.

Thank you for you help

Shane
 
Dear Shane:

Given more details, I believe I could probably write this query.

However, unless you're going to run this query every few seconds, there are
going to be exceptions. There is going to be some period of time when a
foreign key table row (the "one" side of the one-to-many relationship)
contains a row that is "completed" while there exists a dependent row that
is NOT "completed".

I recommend you consider another way of defining how to interpret the data:

1. Whenever the row in the FKT (foreign key table) is "completed" this
counts as completed ONLY when all the dependent rows are also completed.
Otherwise it is ignored. This amounts to a way of interpreting the "live"
data that avoids the problem. Indeed, when this row is completed but there
exists a dependent row that is not, all that is needed is for that dependent
row to become completed and then the FKT row is then interpreted as
completed as well.

2. It may be that you won't even need the FKT "completed" column at all.
Can it ever be that all the dependent rows are "completed" but the FTK row
is NOT considered completed? Or, is it the case that the dependent rows
being "completed" is not sufficient to make the whole thing completed.

Enforcing the rule that the FTK row CANNOT be completed while there is a
dependent row that is not completed is actually the hardest way to implement
this. Please consider the alternatives I've given, and even come up with
some of your own.

Tom Ellison
 
Back
Top