SQL update- where problem

[James Hallam]

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.ptComp lete = -1
WHERE 4ptTable.ptKey = 5vTable.vptKey And 5vTable.vComple te = -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

[Allen Browne]

Re: SQL update- where problem

You could probably do it by using a subquery and including the square
brackets around the names that start with a number:

UPDATE [4ptTable] SET [4ptTable].ptComplete = True
WHERE ([4ptTable].ptComplete = False)
AND EXISTS (SELECT vptKey FROM [5vTable]
WHERE ([5vTable].vptKey = [4ptTable.ptKey])
AND ([5vTable].vComplete = True));

However, I question whether the ptComplete field is justified.
If it should *always* be True if any related record is True,
then it is dependent data, and should not be stored.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"James Hallam" <jahallam@googl email.comwrote in message
news:1154447121 .512316.187330@ m79g2000cwm.goo glegroups.com.. .

>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.ptComp lete = -1
WHERE 4ptTable.ptKey = 5vTable.vptKey And 5vTable.vComple te = -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