Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54

    Question Unanswered: Delete BOTH matching records

    I am trying to create some reports using our invoice history files. The database has two nearly identical records. The first is an "I"nvoice record (same customer number, same apply-to number) and the second is a "P"ayment or "C"redit, with the same customer no. and same apply-to but the dollar amount is exactly opposite (-12.34 vs. 12.34). The two (or more, in the case of multiple partial payments) records net to zero.

    I can easily find the duplicates or use an aggregate query and find the records that net to zero, but how do I delete both records? If the item is paid, I want to kill both records - so that it doesn't clog up the tables and slow everything down. I only want to work with open, unpaid invoices.

    Creating a delete query by matching the data table with the summation query won't work - the records are always locked and won't allow deletion.

    Any ideas? Thanks.

    Matt

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What you're describing sounds pretty damn scary. Unless you're dealing with a few thousand records, I would be retaining your data.

    Have a look at "cascading updates/deletes". It will point you in the right direction if you really want to go that route.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54
    Don't worry, the table I'm using is an imported copy (non-linked) of the original. I want to get rid of the superflous data asap to speed up the app. '

    Not familiar w/ 'cascading updates/deletes.' I'll look for that. Thank you for the lead.


    Matt

Posting Permissions

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