title: How do you delete, update, etc. tables produced by queries in Delphi ADO?
body: <p>I think I am missing something fundamental about working with SQL statements and (Delphi's ADO)Query component and/or setting up relationships between fields in (Access 2003) databases. I get error messages whenever I want to delete, update, etc. anything more complex than than SQL.Text="SELECT something FROM aTable."</p>
  
  <p>For example, I created a simple many-to-many relationship between tables called Outline and Reference. The junction or join table is called Note:</p>
  
  <pre><code>Outline
    OutlineID (PK)
    etc.
  
  Reference
    RefID (PK)
    etc.
  
  Note
    NoteID (PK)
    OutlineID
    RefID
    NoteText
  </code></pre>
  
  <p>I enforced referential integrity on the joins in Access, but didn't tick the checkboxes to cascade deletes or updates. Meanwhile, over in Delphi my Query.SQL.Text is</p>
  
  <pre><code>SELECT Note.NoteID, Outline.OutlineID, Ref.RefID, Note.NoteText, Ref.Citation, Outline.OutlineText
  FROM (Note LEFT JOIN Outline ON Outline.OutlineID=Note.OutlineID)
  LEFT JOIN Ref on Ref.RefID=Note.RefID; 
  </code></pre>
  
  <p>Initially I left out the references to keys in the SELECT statement, producing an 'insufficient key column info' error when I tried deleting a record from the resulting table. I <em>think</em> I understand: you have to SELECT all the fields the db will need for any operations it will be asked to perform. It can't delete, update, etc. joined fields if it doesn't know what's joined to what. (Is this right?)</p>
  
  <p>So, then, how do I go about deleting a record from this query? In other words, I want to (1) display a grid showing NoteText, Citation, and OutlineText, (2) select a record from the grid, (3) do something like click the Delete button on a DBNavigator, and (4) delete the record from the Note table that has the same NoteID and NoteText as the selected record.</p>