Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete records (97 SR2)

    I have a table with multiple records, some of which are exactly the same. These are orders processed. I have another table with records that are orders reversed. When an order is reversed I want to go into the processed table and delete out a matching processed record. The problem is there could be multiple records in the process table and I only want to delete one. There are no unique identifiers on these records.
    Is there a way to do this?
    Thanks for all you help.

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

    Re: Delete records (97 SR2)

    If you have no unique identifier, I assume that it doesn't matter which of the matching orders you delete.

    You will need some kind of identifier to distinguish the records, so:
    - Open the "orders processed" table in design view.
    - Add a field of type AutoNumber, let's say you name it ID.
    - Close and save the design of the table.

    Next, create a query based on the "orders processed" table. Add only the ID field to the query grid. In the Criteria line, enter the following, where tblOrdersProcessed and tblOrdersReversed are the names of the tables, and NameOfField is the name of the field on which you match the tables.

    In (SELECT First(ID) FROM [tblOrdersProcessed] INNER JOIN [tblOrdersReversed] ON [tblOrdersProcessed].[NameOfField] = [tblOrdersReversed].[NameOfField] GROUP BY [tblOrdersProcessed].[NameOfField])

    Select Query | Delete Query. The SQL for the completed query will look like this:

    DELETE ID
    FROM [tblOrdersProcessed]
    WHERE ID In (SELECT First(ID) FROM [tblOrdersProcessed] INNER JOIN [tblOrdersReversed] ON [tblOrdersProcessed].[NameOfField] = [tblOrdersReversed].[NameOfField] GROUP BY [tblOrdersProcessed].[NameOfField])

    Run this query to delete one matching record from tblOrdersProcessed for each record in tblOrdersReversed.

    Warning: run the query only once! If you run it again, it will delete records again, since multiple matches are possible.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete records (97 SR2)

    Thanks so very much!!!!

Posting Permissions

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