1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 03 - dupe numbers

Discussion in 'Business Applications' started by ddw23, Feb 9, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. ddw23

    ddw23 Thread Starter

    Joined:
    Jan 19, 2006
    Messages:
    109
    Hi All.

    I have a db table of vendors for City and County. The vendor number is 8 characters for City (78945612) and 9 for County (the 9th is a "P" - 78945612P). I need a query that will find all dupe vendor numbers (first 8 characters) regardless of the "P".

    Will someone please help?? Thanks!!!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Hello ddw, have a look at the attached database, it has duplicates as you outlined in the table.
    It has 2 queries, the 1st query has an expression using the "Left" string to remove the "P" from the end of the Number.
    The 2nd query is a find Duplicates query based on the first query.
     

    Attached Files:

  3. ddw23

    ddw23 Thread Starter

    Joined:
    Jan 19, 2006
    Messages:
    109
    Thanks, that works very good. I just forgot to state I need to delete the dupes. I'm searching the web but deleting dupes seems to be a complex job! I have approx 7000 dupes. Too much to delete manually.

    Thanks again!!!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    ddw, deleting them is no problem at all, BUT how do you know/decide which one(s) to delete?
     
  5. ddw23

    ddw23 Thread Starter

    Joined:
    Jan 19, 2006
    Messages:
    109
    Right! ThatÂ’s my dilemma. However, I would have to delete the vendor number that ends in P. Some of those would be the dupes to the City vendors. Because the info would /should be identical with the exception of the P. But not all County vendors are dupes to the City vendors.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    ddw, I can write you some VBA code to do the job, if we use the Duplicates Query that I wrote (to cut down on the amount of work) the VB code can scan through that Recordset and delete any duplicates, doing the one with the P suffix first.
    How does that sound, you could test on a copy of the database first to see how it works out.
     
  7. ddw23

    ddw23 Thread Starter

    Joined:
    Jan 19, 2006
    Messages:
    109
    That sounds GREAT!! As long as it only deletes the the P that's a dupe that would be wonderful.....THANK YOU!!!!
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    ddw, I didn't need to write any VBA after all, just some more queries. You have to run the queries in Order so I have created a Form with a Command Button on that does it for you.
    The data is in Table1, the first query that runs puts the duplicates with a "P" on the end in to a new Table called "Duplicates to Delete".
    The Second Query sets each vendor in Table1 that is in "Duplicates to Delete" to "Deleted", Access refused to let me delete the records in this query.
    The third query actually deletes any record where the Vendor is set to "Deleted".
    The fourth query deletes all of the records in the "Duplicates to Delete" Table.
    There is a 3rd table which is a copy of table1 so that I could recteate table1 after the queries have been run.
     

    Attached Files:

  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/542582

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice