Delete Multiple Duplicate Rows

[ElcoyotldeAztlan]

Hello all

I'm working a project and i'm wondering if there is an way to delete duplicate rows fast. I imported a section of my excel sheet that has duplicate rows, at 1st I tried to copy the table and "just structure" and turn one of my fields to No duplicates in the Indeed because this field is the focus of this table and I don't want duplicates the other fields of this table can be duplicates. however it appears some information is lost..granted I know the excel sheet has it's flaws

Any thoughts?
Thanks

 

[arnelgp]

Can you post the field name of the autonumber field also the fields that have duplicate records.

 

[arnelgp]

Okey, here's a template to delete multiple duplicate leaving only the first instance. Note however that for this to succeed your table must have an autonumber field in it:

DELETE (SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.Field1 & T2.Field2= TABLE1.Field1 & TABLE1.Field2) AND T2.AutoNumberField <= TABLE1.AutoNumberField) AS Expr1, *
FROM Table1
WHERE ((((SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.Field1 & T2.Field2= TABLE1.Field1 & TABLE1.Field2) AND T2.AutoNumberField <= TABLE1.AutoNumberField))>1));

you should supply the correct name for:

TABLE1 (actual table name)
Field1 (field with duplicate)
Field2 (field with duplicate)
AutoNumberField (your autonumber field)

in the case you only interested on removing duplicate records on 1 field:

DELETE (SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.Field1 = TABLE1.Field1) AND T2.AutoNumberField <= TABLE1.AutoNumberField) AS Expr1, *
FROM Table1
WHERE ((((SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.Field1 = TABLE1.Field1) AND T2.AutoNumberField <= TABLE1.AutoNumberField))>1));

 

[ElcoyotldeAztlan]

I dont have a link to post my photo (or maybe I dont know because I'm new)

Here is a sample set that's really no different at all from my database expect I have over 3000 Rows that are duplicated and scattered
ID State City County (field names sorry I can't seem to space them)
1 California Los Angeles Los Angeles
2 California Pasadena Los Angeles
3 California Pasadena Los Angeles
4 California Los Angeles Los Angeles
5 California East Los Angeles Los Angeles
6 California Torrance Los Angeles
7 California South Los Angeles Los Angeles
8 California South Los Angeles Los Angeles
9 California San Bernardino San Bernardino
10 California Riverside Riverside

so how could I simple deleted the duplicated rows? Is there a simpler way in Excel?

 

[arnelgp]

DELETE (SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.[State] & T2.[City] & T2.[County] = TABLE1.[State] & TABLE1.[City] & TABL1.[County]) AND T2.[ID] <= TABLE1.[ID]) AS Expr1, *
FROM Table1
WHERE ((((T2.[State] & T2.[City] & T2.[County] = TABLE1.[State] & TABLE1.[City] & TABL1.[County]) AND T2.[ID] <= TABLE1.[ID]))>1));

just replace TABLE1 there with your table name.

 

[ElcoyotldeAztlan]

Thanks!

looks like i will try

DELETE (SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.Field1 & T2.Field2= TABLE1.Field1 & TABLE1.Field2) AND T2.AutoNumberField <= TABLE1.AutoNumberField) AS Expr1, *
FROM Table1
WHERE ((((SELECT COUNT(*) FROM TABLE1 AS T2 WHERE (T2.Field1 & T2.Field2= TABLE1.Field1 & TABLE1.Field2) AND T2.AutoNumberField <= TABLE1.AutoNumberField))>1));

If i understand this correctly this is SQL and T2 represents another table? so I would be transferring the data into a new table correct?

 

[arnelgp]

im sorry, are we in Excel.

in excel, you can do that by first Concatenating those Columns:

supposed: column A contains the State
column B contains the City
column C contains the County

on new column write the forumla:

=$A1&$B1&$C1

copy the formula down to the last row of your worksheet.
select all cells in your worksheet (click on the green triangle betwenn A column and row 1).
on the Ribbon->Data->Remove Duplicates.
tick only the column where you put our formula (=$A1&$B1&$C1). press OK.
your done.

 

[arnelgp]

T2 there is but an alias, it will not make new table.

 

[ElcoyotldeAztlan]

I have the data in both excel and access which way do you think is the easiest way to delete the duplicate row

 

[arnelgp]

In excel, i suppose.

 

[isladogs]

Make sure you backup your data before deleting duplicates just in case.

If you are importing data from Excel, it would normally be easier to remove the duplicates in Excel first

However if the data is already in Access, unless you are very confident with SQL you may find it easier to use the Duplicates Query Wizard.

This will be a SELECT query. It will list duplicates but not delete them.
View the results, decide which can be deleted and either delete manually or change to a suitable DELETE query

 

[Gasman]

Excel has a Remove Duplicates wizard on the data tab.?

I dont have a link to post my photo (or maybe I dont know because I'm new)

Here is a sample set that's really no different at all from my database expect I have over 3000 Rows that are duplicated and scattered
ID State City County (field names sorry I can't seem to space them)
1 California Los Angeles Los Angeles
2 California Pasadena Los Angeles
3 California Pasadena Los Angeles
4 California Los Angeles Los Angeles
5 California East Los Angeles Los Angeles
6 California Torrance Los Angeles
7 California South Los Angeles Los Angeles
8 California South Los Angeles Los Angeles
9 California San Bernardino San Bernardino
10 California Riverside Riverside

so how could I simple deleted the duplicated rows? Is there a simpler way in Excel?

 

[isladogs]

Excel has a Remove Duplicates wizard on the data tab.?

Yes it does and it works well if you don't mind which records are deleted
Excel will keep the first duplicate in the selected column(s) and delete all others

As an example:

For example. running this on both columns or column A only finds no duplicates so nothing is deleted

Running it on column B only results in the following output:

 

[Gasman]

So if the o/p selects all 4 columns, he should get one row for each, and as they are duplicates it does not matter which one?

 

[isladogs]

If the records are identical in all selected columns, it obviously won't matter which are deleted. In that sense Excel works better than Access

But if some columns are different e.g. ID field nothing would be deleted

With Access YOU decide which is deleted.
Whether that is an advantage or disadvantage depends on your viewpoint

Either way, thanks for mentioning the Excel feature as I had forgotten it

 

[ElcoyotldeAztlan]

Thank You guys I got it to work

 

[Gasman]

Well please tell us which method you used.?

It can always help someone else who has the same or similar poblem.