Duplicated rows in access table

[annetiti]

Hallo

I want to delete duplicate records in an access table. There are more rows with the same data, only the id-nr is different. The table contains 96000 rows but not all the rows have duplicates. I try with a query but no result.
I send an extract of the table.

Can somebody help me!
thanks

Anne

[twinnyfo]

Do an aggregate query, with a count on one of the fields that has duplicates. In the criteria, use >1, and it should return all your duplicated rows.

[zmbd]

There is an Access Query Wizard will build this query for you.
BACK UP YOUR DATA!!!
Once the query is bult, and you VERY sure that only the desired records are being returned, you can then run it as a delete action query.

-z

[NeoPa]

I would suggest a query that identifies which records are duplicated. Fine so far, but I assume you want to leave one copy behind of each duplicated record. The next step is then to find the ID value, for each set of duplicates, which reflects the lowest value. With this recordset of values you can find a recordset of ID values that are bot duplicated AND are NOT in the previous recordset. This recordset reflects the IDs of all the records you require be deleted.

[annetiti]

Hallo

I put the duplicated records in a new table. The next step is: "Is it possible to delete records with sql statement"? looking at table 1 for the id and delete in the data table?
Table1 has the id-number of the duplicate records. Table 2 contains the data. I try it with a delete query but it doesn't works!
thanks

[NeoPa]

Annetiti:
The next step is: "Is it possible to delete records with sql statement"?

Indeed it is, but that's a separate question. That means you need to ask it in a separate thread as this one has its own question.

[zmbd]

@NeoPa
NeoPa:
As we're working with the same data... and OP has made an effort perhaps a slight nudge in the right direction? After all... OP was about using a query to accomplish the task

Annetiti:

This doesn't follow in your OP as you wanted a single query to do the work on a given table... what you now have is the question: "How do I delete records in one table that match records in a second table"
Two tables...
tbl_withallofthedata
tbl_deletetheserecords

What ever you do here... MAKE A BACKUP of the tbl_withallofthedata!

Just because I like to reward a good effort: slight nudge... you need a query, show both tables, link, etc...

-
In a answer to your OP, yes a query can manage this...
You will need a query to pull the duplicate records (posts #2 and #3). The wizard isn't elegant, but it works.

You then need to look at this query and make a choice as to which records to keep and what field(s) will allow you do this... date/time fields.... primary keys... but you must have a unique means (post #4) so that you can exclude the records you want to keep create a new query based on the wizard results.

Now once you have a query that shows only the records to delete... then you can run the query as a delete query.

This is not difficult, just a lot of little queries to build or a lot of SQL lines to type.

BACK UP your data... and give it a try and post back the SQL that you created.

-z

[zmbd]

A fairly indepth reveiw of duplicate records and how to deal with them in a bunch of different ways... some not as I would do; however, a fairly indepth piece:
http://office.microsoft.com/en-us/ac...001034558.aspx

IMHO: this should answer, or point the way to an answer, for just about every question on duplicate records. I know I just picked up a trick or two from it myself!

-z

[NeoPa]

That really would have worked a lot better if you'd waited for the OP to post the question properly and replied to that Z (as I was trying to indicate is what we expect).

That way, your helpful post would be more likely to be seen by people. As it is, only those looking for something along the lines of the original question are likely to see it, which is a bit of a waste.

You see, we do consider things before we make up rules. They really do benefit everybody involved if you think about it (You, the OP, the site, everybody).