Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    unselect multiple records (Access XP)

    I am attempting to delete duplicate records. I am searching for duplicate records based on an address. I can sometimes have two addresses the same but only want to keep one address on file.
    In my database I use an update query to tick a tick box where the record is duplicated. I then manually eyeball each ticked record and either deselect it or leave it ticked. I then run a delete query to delete those records that are ticked. Is there a way that I can automatically deselect every second record? This would save me having to deselect every second record manually.
    thanks...Peter

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: unselect multiple records (Access XP)

    Provided each record has a unique record identifier field (like an autonumber), perhaps there is a way. In that query that initially detects and "ticks" duplicates, add a selection critieria that will only tick those records where its ID# is higher than the other record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: unselect multiple records (Access XP)

    To build on this, is the address itself unique, or is it the address and some other fields that make the records unique?

    If you delete each duplicate address, will you inadvertently delete wanted data as well?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    New Lounger
    Join Date
    Jan 2002
    Location
    Black Earth, Wisconsin, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unselect multiple records (Access XP)

    I have a some what similiar problem. I have 2 tables of product information, one from each of our warehouses. I needed to match like items from each warehouse via Case UPC codes. (1 UPC code but 2 different items) I created a query that matches the 2 tables by case UPC code. This query now has each warehouse's like items information in one row. Then I created a report from that query which shows the matched information, one facilities' item information above the other. Now I am being asked if I can delete the like items that have the same comm code. Both Comm Code fields are found in the same row of information. I have attempted a delete query but haven't been successful with the criteria. I have no programming skills. Any help would be appreciated. I tried to attach the db but it is too large. I also attempted to attach it as an Excel spreadsheet but that was also too large. Hopefully this explains what I need to do. The Lounge has been helpful in the past and I appreciate any help at this time. Thanks in advance.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: unselect multiple records (Access XP)

    I'm not sure I follow you when you say "This query now has each warehouse's like items information in one row". Do you mean if there are 2 items the same they appear on the same line in the query?

    Before you can proceed, is there anything unique about the 2 records that share a UPC code? Without that, it will be hard to delete anything. Essentially your thinking is this:
    Examine a record.
    Delete that record if it has a duplicate, but only if its unique record ID is greater than duplicate's ID.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Jan 2002
    Location
    Black Earth, Wisconsin, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unselect multiple records (Access XP)

    I was afraid I wasn't going to be able to explain it correctly. I have exported the table to Excel and attached. If the first two fields match the row needs to be deleted. If the first two fields do not match, the row stays.
    Comm Code 909 CommCode 909 ----- the row needs to be deleted
    Comm Code 910 CommCode 912 ---- The row stays.
    It is probably very simple but I can't seem to come up with the solution. Thanks.
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Jan 2002
    Location
    Black Earth, Wisconsin, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unselect multiple records (Access XP)

    I figured out a way to do what I need. It isn't pretty and takes some time. I created a delete query where I placed "Like 909" in the first field and "909" in the second field. I had about 150 "likes" I had to do but it worked. Took the table from over 11,000 rows to a little over 5,000. If there is an easier way I would sure be interested. Thanks.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: unselect multiple records (Access XP)

    I think I understood it. You said:
    Comm Code 909 CommCode 909 ----- the row needs to be deleted

    This means that there are 2 records out there with CommCode 909, correct? So, which one do you want to delete?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: unselect multiple records (Access XP)

    The delete statement you need is probably the following (as long as I understand what you are doing, where the two fields are in the record and are Comm Code and CommCode):

    DELETE * FROM Table WHERE [Comm Code] = CommCode;

    HTH
    Pat {cheers]

Posting Permissions

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