Field update based off of child relationship

[Toguspyder]

I am trying to figure out how to have a parent field update when all children records have met criteria.

For example I want the Parent field "Complete" which would be a yes/no to update when all of the children field "ChildComplete" is marked yes.

I have need for many uses (in different variations) on this, but I am not well versed in SQL or VB. Any kick in the right direction would be helpful.

Thanks in advance.

 

[lespaul]

when all of the children field "ChildComplete" is marked yes.

Does this mean you have multiple fields of "ChildComplete"?

How do you know when this condition is met to apply the update to the Parent table/field?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Why storing a derived/calculated value ?
Have a look here:

http://r937.com/relational.html

Info retrieval (SQL code):
SELECT P.PrimaryKey, P.SomeField, P.AnotherField, (Sum(Abs(C.ChildComplete))=Count(*)) AS Complete
FROM ParentTable AS P INNER JOIN ChildTable AS C ON P.PrimaryKey = C.ForeignKey
GROUP BY P.PrimaryKey, P.SomeField, P.AnotherField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Toguspyder]

Lespaul

I have two tables with a one to many relationships. Both have a similar field to them (ex. Complete) and I want the parent field to update when all of the children that are related to that record have been updated. So if I have a parent record with 3 child records to it, and all the child records have been “marked complete”, I want the parent to recognize that the children are “complete” and update to show that, totally it is complete.

PHV

Thanks for the info. I will give it a try today and post to let you know how it worked.