deleting duplicate values based on 2 fields

[jamesmor]

Hi guys,

I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.

companyname zip
billybobs 68135
dilberts 68137
billybobs 68135

what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?

Any help is greatly appreciated

Thanks

James

 

[reclusivemonkey]

You could try the "find duplicates" query...

 

[jamesmor]

so do I just make the find duplicates query and turn it into a delete query?

 

[reclusivemonkey]

That *should* do it. As long as this is all from one table. If you have any trouble, make two; the "find duplicates", then use this in a seperate delete query.

 

[jamesmor]

so here's what I did, I ran the find duplicates query wizard, then changed it from a select query to a delete query using the examples above and the query wanted to delete all 3 records.

 

[reclusivemonkey]

There's something wrong with the query then. Keep them seperate, so you don't delete anything before you know the query is getting the correct records.

 

[jamesmor]

well, when I run the query in the "select" view it only gives me the correct records...

 

[reclusivemonkey]

Ok, well seperate the duplicate and delete queries; use the duplicate one as the start point for the delete query.

 

[jamesmor]

just tried that, still no love, any chance I can get you to throw up a quick example mdb?

 

[reclusivemonkey]

just tried that, still no love, any chance I can get you to throw up a quick example mdb?

Yeah, sorry it wasn't quite as simple as I said... might take a while for the old brain to engage after the holidays ;-)

 

[jamesmor]

thanks man, I haven't been able to play with it yet, won't till tomorrow, but from first looks it should work out ok.

 

[jamesmor]

actually got to it just now, still no love. I double clicked on your delete query and it said "Specify the table containing the records you want to delete" so I opened the query and just drug down the star from tblcompany, and I get "Could not delete from specified table"

 

[jamesmor]

I'm still having problems, anyone willing to give it another shot? If needed I can upload part of the db that I'm working with.

 

[reclusivemonkey]

I'm still having problems, anyone willing to give it another shot? If needed I can upload part of the db that I'm working with.

Yeah, post it and I will take a look.

 

[jamesmor]

ok, here it is, I've stripped it down to the 3 fields that I need to work with.

I need to remove duplicate entries where the company name and zip are the same. I want to keep the record with the most recent invoice date. now I chopped this down to only 5k records however, when I do this it will be against ~ 120k records.

There may or may not be duplicates in this list also.

 

[reclusivemonkey]

ok, here it is, I've stripped it down to the 3 fields that I need to work with.

OK James, I am just on my way out of work now, so I will take a look at this tomorrow.

Hmm, it seems I don't know anything about delete queries. I was sure I had done something very similar some time ago. It should be possible to do via some VBA, however I have a couple of questions first. Is your DB normalised? You shouldn't have invoice details in with your customer details. Secondly, is this a one off task you are doing? If so, thats understandable, but your problem here stems from your DB not being normalised. If you do this, you should be able to eliminate any duplicates being produced.

 

[jamesmor]

this is a one off task that I'm doing, normally the invoice details aren't in there, but for this particular project I need the customers latest invoice date. normally there's other fields in the table like a contact name, but I didn't have the space to put up all the fields. I figure I can deal with them when I get to them.