[AccessD] Can't delete records from this table

A.D.

What is interesting (and I didn't notice it immediately) is that the records
on the one side was also deleted which is NOT supposed to happen.  The
situation is as follows:

TABLES:

xlsWP - with a field _IDCLM to hold the PK in the claim table
tblClaim - CLM_ID is the PK.
tblClaimLTD - CLMLTD_ID is the PK.  1-1 with tblClaim, with the relationship
established in the BE.

I designed the query such that the join was between xlsWP._IDCLM and
tblClaimLTD.CLMLTD_ID (the PK in tblClaimLTD), select * from tblClaimLTD,
then turn that into a delete query.  Thus NO FIELDS from xlsWP are visible
when it is a select, it is just used for the join to select records in
tblClaimLTD.  When turned into a DELETE this SHOULD function correctly, no?
But NO, it complains and refuses to do the delete.

Anyway, if I turn it back into a select I can view the data in tblLTD and I
can select the records (manually) and delete them and they do delete,
however... The records in xlsWP ALSO DELETE which they should not!!!

I have built a workaround where I pull xlsWP, joined xlsPW._IDCLM to
tblClaim.CLM_ID, then join tblClaim to tblClaimLTD.  Select * from tblLTD.
Turn THAT into a delete query and voila, the delete works without complaint,
ONLY the records in tblClaimLTD are deleted and I am happy.  Now, WHY do I
need to "go through" tblClaim?  The PK in tblClaim is 1-1 with tblClaimLTD.
The inner join between xlsWP and tblClaimLTD should function the same as
xlsWP and tblClaimLTD.

The mysteries of Access!!!

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Friday, August 17, 2007 10:36 AM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com