Deleting duplicates

[Kim Jackson]

I have a table for partnumber and it has a lot of
duplicate i have done a query that give me the count of
each partnumber and how many are duplicates. I need to
know how to delete all but one of the part number.
thanks

 

[Immanuel Sibero]

Hi Kim,

You can use the same concept of the query you built to get the count.

- Create a query containing the partnumber table, including all its fields.
- Turn this query into a group/summary query. You would want to group the
query by "partnumber" field. For the other fields, you would choose "First",
"Last", "Sum", "Avg" as you wish (see note below).
- Turn this query into a "Make Table" query, and specify a name of the new
table.

After you execute the above query, a new table is created without duplicate
partnumber values.

NOTE: When you execute this query, Access creates one record for each
partnumber. If you have duplicate records for a partnumber, the values for
the other fields will depend on the grouping option you selected (ie. First,
Last, Avg, Sum, etc. etc.) So, depending on what you currently have in your
partnumber table, there is a potential loss of information. For example,
duplicate records with different values for a given field.

HTH
Immanuel Sibero

 

[Guest]

Immanuel,
It worked.. Thank you very much..

-----Original Message-----
Hi Kim,

You can use the same concept of the query you built to get the count.

- Create a query containing the partnumber table, including all its fields.
- Turn this query into a group/summary query. You would want to group the
query by "partnumber" field. For the other fields, you would choose "First",
"Last", "Sum", "Avg" as you wish (see note below).
- Turn this query into a "Make Table" query, and specify a name of the new
table.

After you execute the above query, a new table is created without duplicate
partnumber values.

NOTE: When you execute this query, Access creates one record for each
partnumber. If you have duplicate records for a partnumber, the values for
the other fields will depend on the grouping option you selected (ie. First,
Last, Avg, Sum, etc. etc.) So, depending on what you currently have in your
partnumber table, there is a potential loss of information. For example,
duplicate records with different values for a given field.

HTH
Immanuel Sibero





.

 

[Guest]

Make a copy of the table you want to get rid of the
duplicates in. Paste it as structure only. In the new
table you created, make the part number the primary key.
Then make an append query with the two tables in it.
Append the data from the old table to the new one that has
no data. Because you made the partnumber the primary key,
it will not allow duplicates, and will tell you how many
records it could not append.