Deleting duplicated records

[SChua]

How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
Thanks

 

[jdraw]

How did duplicate records get into the table? If you get rid of them, what's to prevent it happening again?

I think you should research Primary Key, and Unique index.

 

[CJ_London]

Post #10 on this link should provide what you need to remove the duplicates (thread went off in a different direction after that!) but I agree with JDraw - you need to review how the duplicates are happening to prevent them in the future (unless it's someone elses data in which case get them to do it)

http://www.access-programmers.co.uk/forums/showthread.php?p=1251800#post1251800

 

[SChua]

The reason for the duplicates is that each period, I download data (eg, sales data) into an Access table for analysis. If the date range for the data downloaded overlaps between one period and another, then I will have duplicates. As a precautionary matter (because this can happen just due to human error) I would like to find duplicates and delete them.

I looked at the link provided. Am i right in that the way the query is written in that example, it deletes records that have duplicates in one particular field (MRN). Is there a way to find and delete records that are duplicated in all fields in the table?

 

[CJ_London]

Is there a way to find and delete records that are duplicated in all fields in the table?

Yes - if you follow the 3 examples in that post you should get there.

1. Create find duplicates query - follow the prompts and list all the fields you consider to be duplicates
2. Add in the subquery to find (in this case the oldest date) whatever record you want to delete - in your case if your data has a timestamp field you will want to delete the latest timestamp (ie. =(SELECT Max(TimeStamp)...) - (if you don't have a timestamp field, I strongly recommend you add one, they're very useful!)
3. Change the query to a delete query

It is generally better not to have duplicates in the first place, so moving forward I recommend you look at your insert query with a view to adding a subquery which checks if the data already exists and if it does, excludes it.

 

[SChua]

Thanks for the lead ... I did not know about the insert query and its ability to exclude duplicates. So that would work at the point of appending data to a table? Would you be able to point me to some leads the insert query?

 

[CJ_London]

You have to design the query - it is not actually a built in function and there are anumber of ways it can be done depending on what conversions are required

If you are importing table a to table b and for the sake of this example there are 3 fields in each table called X, Y & Z

At the moment you probably have a query along these lines:

INSERT INTO B (X, Y, Z)
SELECT A.X, A.Y, A.Z
FROM A;

This is the simplest example and will probably meet your needs

INSERT INTO B (X, Y, Z)
SELECT A.X, A.Y, A.Z
FROM A LEFT JOIN B ON (A.X=B.X AND A.Y=B.Y AND A.Z=B.Z)
WHERE B.X Is Null AND B.Y Is Null and B.Z Is Null;

 

[SChua]

I am having trouble trying it out. I opened a query design and went to sql view and tried to insert it (with the changes to the name of tables and variables). Is this what I should be doing?

 

[CJ_London]

Yes - it should look something like the attached.

This example just appends to itself (and won't actually append anything because the record already exists) but should be sufficient to demonstrate the principle

In this example Table4 is your table to be imported and Table4_1 is the table you are importing to

 

[SChua]

Hi, I am still having trouble with the "Insert" part.
I replicated your screenshot with sample tables and that worked - it gave me the non-duplicated records. When I added the first part with the Insert statement, it gives me the message "Syntax error in INSERT INTO statement". See below for what I used.

//(this part does not work)//

INSERT INTO unique values - all fields (Location, Item No, Description, Customer Name, Sum of Revenue, Total Quantity, Product Category, Customer Type)

//(this part below works fine)//
SELECT test.Month, test.Location, test.[Item No], test.Description, test.[Customer Name], test.[Sum of Revenue], test.[Total Quantity], test.[Product Category], test.[Customer Type], [unique values - all fields].Month, [unique values - all fields].Location, [unique values - all fields].[Item No], [unique values - all fields].Description, [unique values - all fields].[Customer Name], [unique values - all fields].[Sum of Revenue], [unique values - all fields].[Total Quantity], [unique values - all fields].[Product Category], [unique values - all fields].[Customer Type]
FROM test LEFT JOIN [unique values - all fields] ON (test.Month = [unique values - all fields].Month) AND (test.Location = [unique values - all fields].Location) AND (test.[Item No] = [unique values - all fields].[Item No]) AND (test.Description = [unique values - all fields].Description) AND (test.[Customer Name] = [unique values - all fields].[Customer Name]) AND (test.[Sum of Revenue] = [unique values - all fields].[Sum of Revenue]) AND (test.[Total Quantity] = [unique values - all fields].[Total Quantity]) AND (test.[Product Category] = [unique values - all fields].[Product Category]) AND (test.[Customer Type] = [unique values - all fields].[Customer Type])
WHERE ((([unique values - all fields].Location) Is Null) AND (([unique values - all fields].[Item No]) Is Null) AND (([unique values - all fields].Description) Is Null) AND (([unique values - all fields].[Customer Name]) Is Null) AND (([unique values - all fields].[Sum of Revenue]) Is Null) AND (([unique values - all fields].[Total Quantity]) Is Null) AND (([unique values - all fields].[Product Category]) Is Null) AND (([unique values - all fields].[Customer Type]) Is Null));

 

[CJ_London]

You have spaces in your names (bad practice!) so you need to surround them with square brackets

Location, [Item No], Description, [Customer Name]...etc

 

[SChua]

Thanks! I am making progress but now getting the error "Number of query fields and destination fields are not the same".
First part of query now reads:
INSERT INTO [unique values - all fields]([Month], [Location], [Item No], [Description], [Customer Name], [Sum of Revenue], [Total Quantity], [Product Category], [Customer Type])

I have checked to make sure that all fields are included (I added [Month] into the last part following the WHERE statement - this was missing)..
What am I still doing wrong?

 

[CJ_London]

You'll need to post your whole query not part of it!

Also Month is a resreved word and can produce unexpected results - best to rename

 

[SChua]

Hi, I figured it out, I had too many fields after the SELECT statement (it included the same fields from both tables when I should have specified only one table).
Thanks a million for your help!