Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: Delete query on related tables

    Hi everyone - I've got what I believe is a simple problem but I'm not seeing the solution clearly.

    The situation is this: Two tables, one named tblLoan and one named tblProperty. The tblLoan table is one-to-many with tblProperty. The fields Pawn and Contract are the same thing in each table – a 5 digit number that joins them and relates one or more pieces of property to a loan on said property. Enforce referential integrity, cascade update, and cascade delete are all checked in table relationship.

    What I want to do is have the user be able to delete records where tblLoan.Pawn = 41269, for example, and cascade delete all records in tblProperty where tblProperty.Contract = 41269. In other words the loan is deleted and all related records from the Property table are deleted also. Note that currently a record can exist in tblLoan WITHOUT a related record in tblProperty.

    Here’s the delete query I have:

    DELETE DISTINCTROW tblLoan.* , tblLoan.Pawn FROM tblLoan
    INNER JOIN tblProperty ON tblLoan.Pawn = tblProperty.Contract
    WHERE (((tblLoan.Pawn) = [Enter Loan Number]));

    Right now it works fine if there is a value 41269 in BOTH tables. What I need to fix is if for whatever reason if tblProperty.Contract does not have a value = 41269, it deletes the row in tblLoan.Pawn anyway. (Basically the user is allowed to enter Loan data without entering information regarding property that the customer took out a loan on…so no record in tblProperty is created unless the user specifically inputs it.)

    Currently if there is no related record in tblProperty, a msgbox pops up that says "You are about to delete 0 records. Are you sure?" The expected result should be 1 record...(in tblLoan).

    Can anyone help? Would greatly appreciate it!!!

    dnadler27 at hotmail dot com

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If your relationship between the two tables is set to Cascade Delete, than
    any deletions from the "one" side will automatically delete related records in the "many" side.

    Therefore, your delete query only needs to look like this:

    DELETE *
    FROM tblLoan
    WHERE (((tblLoan.Pawn) = [Enter Loan Number]));
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    Well that was easy. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •