Update Record Based on Related

[GendoPose]

Hi All,

I have 2 tables, one is like a main table, containing all of the main data, such as a Job Number, Customer, Quantity, etc. I have a second, related, table that acts a breakdown of information. There may be several related records to one main record, it entirely depends on the nature of the job.

What I'd like to do is run a function that looks at a main record, checks if all the related records COMPLETED field is ticked and then tick a field in the main record. I only want it to do this for records where all of the related records are COMPLETED.

How would you guys go about this? I'm pretty stumped for ideas right now so any help is appreciated, and of course ask if you need more info.

Thanks!

 

[pbaldy]

For a lot of us the answer to "how would you do it?" is "we wouldn't". You're storing a calculated value, which is generally a bad idea. I would probably create a little function that accepted the linking field (job number?) and returned a Boolean value representing completed or not. I'd call that in forms/reports to display the status, but if you really wanted you could also use it to update.

 

[GendoPose]

For a lot of us the answer to "how would you do it?" is "we wouldn't". You're storing a calculated value, which is generally a bad idea. I would probably create a little function that accepted the linking field (job number?) and returned a Boolean value representing completed or not. I'd call that in forms/reports to display the status, but if you really wanted you could also use it to update.

Well the end result will just be ticking a field, but I understand where you're coming from. Even before the update, how could I call up all of the main records that have all their related records marked as COMPLETED? I assume this would be an expression in a query, but what specifically would I write?

 

[pbaldy]

SELECT KeyField, ...
FROM MainTable
WHERE FunctionName(KeyField) = True