duplicates

[N i M d A]

Hi all,

I have 4 text fields in access (field1,field2,field3,field4) which contains
duplicate data (more than few 100),
Is there any way to delete everything duplicate that I have?
I don't know if Access 2007 has the features to remove duplicates without
writing to much code, I'm using 2003 ver.

Thanks in advance
nimda

 

[Steve Schapel]

Nimda,

Do you mean the *combination* of the 4 fields is duplicated? Maybe you
could give some specific examples of the data you are working with, and
what the required outcome might be.

 

[N i M d A]

Hi,
okey here is an example.

field1, field2, field3, field4
john , smith, 1245, co
john , smith, 1245, co
Tony, lee, 4543 , xd
Tina, climson, 6545, th

as you see I have 2 time John with same information,
this is why I mean removing duplicates,
I know that I can a query to check all my duplicates but since they are a
lot it will be difficult to delete them manually.

Thanks guys

 

[Steve Schapel]

Nimda,

Follow these steps, making sure first that you have a backup copy of the
database:

1. Make a copy of your table, with exactly the same structure as your
existing one, but with no data.

2. Make a Query based on your existing table, and add all fields to it.

3. Right-click anywhere on the background of the upper panel of the
query design window, and select Properties from the popup menu.

4. Set the Unique Values property to Yes.

5. Make this query as an Append Query (select Append from the Query menu).

6. Nominate the new table created as per step 1 above, as the table to
append to.

7. Run the query (click the toolbar button with the red [!] icon.

8. Delete the original table.

9. Rename the new table to the name of the original.

 

[Jeff Boyce]

Uhmmmm, are you very certain that you don't have two "John Smith"s in your
database? It isn't uncommon for two people to share the same name...

As others have pointed out, a query with Unique Values = Yes might be what
you're after...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve Schapel]

Tkelley,

Sure. As a general "rule of thumb", I would say *never* try to work
with data in a database using the copy/paste concept. It's just too
shaky. There are lots of data manipulation tools provided - action
queries being a good example.

 

[Steve Schapel]

Agreed. We don't know where this data is coming from, how the
duplication occurs, what else is in the database, etc, but some sort of
system for ensuring accurate uniqueness on-going will be required for sure.

While I'm at it, Nimda, I suggest you also consider something other than
field1, field2, field3, field4 for your field names. It helps a lot if
you call them something that gives some indication of their purpose.

 

[N i M d A]

Thanks guys,
your idea's worked I really appreciate your time & knowledge.

Regards
nimda