Removing Duplicates?

[hockeyadc]

Hi all, I'm trying to check for duplicates in a table, and if found, delete the corrosponding row. There are several thousand records in the table however, so I'm also trying to run this as fast as possible.

Any help would be great! Thanks!

 

[plog]

What fields are in your table, of those fields what constitutes a duplicate? Do you have a unique, autonumber primary key field? Please provide some sample data as well.

 

[hockeyadc]

What fields are in your table, of those fields what constitutes a duplicate? Do you have a unique, autonumber primary key field? Please provide some sample data as well.

I need to just have the rows that have unique emails and phone numbers. I can make each of these a key and run a query twice but then how do I remove the entire row which contains a duplicate?

I'm sorry but I can't provide sample data for this as the data is confidential...

 

[plog]

If you don't have a unique primary key autonumber field, don't worry about deleting data. You should create a query which will have all of your "unique" rows of data. Do this by:

1. Create a new query in Design view, bringing in your table as the source.
2. Bring down all the fields you want.
3. Make the query and aggregate query by clicking the sigma icon in the ribbon (Greek letter that looks like a capital E).
4. Underneath every field that isn't the Email or Phone Number field, change the 'Group By' to 'First'.
5. Save the query and that is your unique data.

If your set on deleting rows, let me know and I can walk you through that.

 

[hockeyadc]

Thank you, I will try it soon but I have ran into another issue. I'm sure its pretty simple, here is what I'm trying to do:

SELECT City from [Cities] where Cityname='[Datatbl].area';

Now I'm getting an empty table from this, I assume my method is incorrect. My ultimate goal is to take data from column1 of table1, compare it to column1 in table2, and take corrosponding data in column2 of table2 and put it in column2 of table 1...

 

[hockeyadc]

Sorry this is more like what I'm trying to do...

INSERT INTO Table1 (Column2)
SELECT Column 1
From [Table2] where Column3='[Table1].Column1';

When I do this it says it will append 0 rows of data and it doesn't work.

 

[plog]

When you are putting data into records that already exist that is an UPDATE query. An INSERT query is used to add completely new records to a table.

My ultimate goal is to take data from column1 of table1, compare it to column1 in table2, and take corrosponding data in column2 of table2 and put it in column2 of table 1...

Based on that description, I believe this is the SQL you want:

UPDATE Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1 SET Table1.Column2 = [Table2].[Column2];

 

[hockeyadc]

Thanks alot man that did the trick!