Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query (Access 2000)

    I need to build a delete query about offers that does the following

    1. deletes all offers that have not corresponding row either in the offers or in the offer details
    2. deletes all the offers that have no unit price
    3, deletes al offers that have no customer

    My query evidently is wrong, is it possible to do al the task with one query ?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query (Access 2000)

    The condition "deletes all offers that have not corresponding row either in the offers (...)" makes no sense. Each offer obviously has a row in the offers table, otherwise it wouldn't be an offer.

    Why does the TblOfferDetails table contain a field ClientID? The table is linked to TblOffers on OfferID, and TblOffers also has a field ClientID.

    The TblOfferDetails table is meaningless without other fields.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query (Access 2000)

    Thank you for the reply.Your are absolutely right, the field Customerid should not exist in the tblofferdetails I simply made an error when trying to simplify the database in order to send it as an attachement.
    As for my question i wil give you an example.
    In the table tblorderdetals i want to delete all the offers where the unitprice is 0 My query does delete them:
    DELETE TblOfferDetails.*, TblOfferDetails.UnitPrice
    FROM TblOfferDetails
    WHERE (((TblOfferDetails.UnitPrice)=0));
    However, for example order 10, is dleleted from this table but stays put in the table tbloffers.How can i delete these offers also from both tables ? I cannot use the find unmatched query choice and i have to write a code

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query (Access 2000)

    I have made a code for deleting offerid in the table TblOffers when the offerid is not present in tblOfferdetails but i receive the message"specify the table contaning the records you want to delete.But this table is TblOffers ?

    DELETE tbloffers.offerid, TblOfferDetails.offerid
    FROM TblOfferDetails LEFT JOIN tbloffers ON TblOfferDetails.offerid = tbloffers.offerid
    WHERE (((TblOfferDetails.offerid) Is Null));

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query (Access 2000)

    In the first place, you should create a relationship between the two tables on the OfferID field. Tick the check boxes "Enforce referential integrity" and "Cascade delete related records" in the Edit Relationships dialog.

    In your sample database, there are no records with UnitPrice = 0. But let's assume that you want to delete records where UnitPrice is either blank or 0. You can use the following SQL:

    DELETE offerid
    FROM TblOffers
    WHERE offerid In (SELECT OfferID FROM TblOfferDetails WHERE Nz(UnitPrice,0)=0)

Posting Permissions

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