Deleting 'detail' records also deletes 'master' 1

[dhouchin]

Hi,

I am having trouble creating a list of records that I can delete.

I have three tables that are set up (more or less)as follows:
[tt]
LOCATIONMASTER DETAIL STATIONMASTER
LocationID ---> LocationID
LocationName StationID <-- StationID
Date StationDescription
Acct
Amount

[/tt]
No explicit relationships are defined for these tables.
I create a query that lists only the fields in the DETAIL table. I define the relationships within the query as indicated above.

I specify certain LocationNames and StationDescriptions, but do not show these fields in the result set.
The resulting recordset is not updateable. I don't understand why.

When I set the query to allow inconsistent updates, deleting a record from the DETAIL file, cascades the delete and also deletes the corresponding records from the LocationMaster and the StationMaster.

What am I failing to understand about this process? Why am I unable to correctly delete records from this table? Is there a smarter way to achieve this?

Thanks for your help.

Dennis

 

[tlbroadbent]

How are you creating the query? Do you select the LocationID and StationID in the query? Access requires key columns to identify which records to delete.

I recreated your tables with abbreviated names. Then I created the following query which is updatable and only allows deletions of detail records when viewed in data view.

SELECT DtlTbl.LocId, DtlTbl.StatID, DtlTbl.DtlDate, DtlTbl.DtlAmt, DtlTbl.DtlAcct
FROM (DtlTbl INNER JOIN StatTbl ON DtlTbl.StatID = StatTbl.StatID) INNER JOIN LocTbl ON DtlTbl.LocId = LocTbl.LocId
WHERE LocTbl.LocDesc=&quot;abc&quot; And StatTbl.StatDesc=&quot;xyz&quot;;

I could also successfully run the query as a delete query.

DELETE DtlTbl.*
FROM (DtlTbl INNER JOIN StatTbl ON DtlTbl.StatID = StatTbl.StatID) INNER JOIN LocTbl ON DtlTbl.LocId = LocTbl.LocId
WHERE LocTbl.LocDesc=&quot;abc&quot; AND StatTbl.StatDesc=&quot;xyz&quot;; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[dhouchin]

Thanks for your help, Terry. Once you confirmed that I was using the correct structure for creating an editable, deletible query, I began digging into the structure of the tables themselves.

I found that I had somehow removed the 'primary key' status from the stationid field, and that was the cause of the problem. When I changed it back to a primary key, it worked perfectly.

Do you understand the reason behind this behavior, since the field in the other file was not even part of the select statement? I thought that 'allow inconsistent updates' would be less restrictive about whether a change would cause an invalid relationship - ie, allow you to change a key field in a master record without changing the detail records.

Any further insight you could give into this process would be very appreciated.

Thanks,

Dennis