[AccessD] Can't delete records from this table

John,

    It can be regarded as a case of optical illusion. When you open it as a select query (inner join on one to many relation) and delete the selected records manually, the records in the second table (the one on many side of the join) are the only ones that are getting deleted. However, inner join between the tables forces the query to display no output. Even if it is a LEFT join, the output will appear temporarily lost. On closing & re-opening the query, all records from first table will get displayed, showing null values in fields pertaining to second table.

    Delete query involving inner or outer join will also perform the above job successfully, if the qualifying table's name is changed to that on many side of the join.

    If it happens to be a one to one join on primary keys, either of the two tables, or both can be specified in the delete query and it will perform smoothly. Manual deletion via select query involving one to one join affects both tables. Equivalent delete query specifying both tables would be:

=================================
DELETE T_A.*, T_B.*  
FROM T_A INNER JOIN T_B ON T_A.ClientID = T_B.ClientID;
=================================

    Note (one to one relationship):
    (a) T_A & T_B are the table names. ClientID is PK field on both.
    (b) With inner join, only the records where PK values are common to both tables, will get deleted.
    (c) If it is desired to delete all records in both tables, the join should be changed to LEFT or RIGHT type depending upon which table has extra records.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, August 17, 2007 02:47
  Subject: [AccessD] Can't delete records from this table


  I have a table where if I create a simple query - join tblX to tblY on PKID, turn into delete * - it gives me the message "can't delete records from this table".  Yet if I turn it back into a select query I can highlight records and delete them.  

  Any ideas why?

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com