I have a parent record with a child record.
The parent record contains structures and the child records contains
visits to those structures.
Each table has a field named "Complete", what I want is the structure
'complete' to be changed to true when ANY of the visits have been
completed.
I have tried to implement SQL to do this, but it gives me the error
"Syntax Error (Missing Operator)..." when I try and save the query. I
have searched through the forums and couldn't find anything relevent to
this.
My SQL code (the error is on the last line i think):
UPDATE 4ptTable, 5vTable
SET 4ptTable.ptComplete = -1
WHERE 4ptTable.ptKey = 5vTable.vptKey And 5vTable.vComplete = -1
Where 4ptTable is the parent table (structures), 5vTable is the child
table (visits), ptComplete is the field I wish to update (with the
value -1 [true]), ptkey is the primary key and vptKey is the foriegn
keym vComplete is whether the visit is complete.
? Am I right in thinking I need the primary key to be equal to the
foreign key - otherwise it will update all records in the recordset.
? I may also need to change this so that the parent record will change
only when ALL the child records are completed - any ideas.
I'm hoping that it is just my basic SQL code that is wrong...
grr, why can't SQL be as intinctive as VBA!
Cheers,
James Hallam