×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Deleting 'detail' records also deletes 'master'

Deleting 'detail' records also deletes 'master'

Deleting 'detail' records also deletes 'master'

(OP)
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:

LOCATIONMASTER     DETAIL         STATIONMASTER
 LocationID ---> LocationID
 LocationName    StationID   <-- StationID
                 Date            StationDescription
                 Acct
                 Amount


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

RE: Deleting 'detail' records also deletes 'master'


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="abc" And StatTbl.StatDesc="xyz";

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="abc" AND StatTbl.StatDesc="xyz";

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.

RE: Deleting 'detail' records also deletes 'master'

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close