Solved: Access 03 - dupe numbers

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!!!
 

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.
 

Attachments

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!!!
 

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?
 

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.
 

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.
 

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!!!!
 

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.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top