How do I change the value of a MS Access field based on the value.

[LBuckner]

I'm using MS Access 2002. I want to update the value of a field based on the
value of several records. Example: I have ten records that roll to a main
record. If all ten records are marked as complete, I want the main record to
be completed. If one record is left open the main record should remain open
until everything is complete.

 

[Alex Dybenko]

This is a typical task for trigger, if you would have SQL server table. but
access does not have triggers. what you can do - is to make a procedure
which checks value of ten records, and accordingly updates main record, and
you have to call this proc each time you make updates to this table - in
form's afterupdate event, etc

 

[Tim Ferguson]

If all ten records are marked as complete, I want the main record to
be completed. If one record is left open the main record should
remain open until everything is complete.

This will give a list of MainRecordIDs and whether the main record is
open or closed:

SELECT MainRecordID,
10=COUNT(*) AS MainRecordClosed
FROM Records
GROUP BY MainRecordID
WHERE Completed=TRUE
ORDER BY MainRecord;


so perhaps you don't need to denormalise the database at all!

Hope it helps


Tim F