Duplicate Data

[Guest]

I have about 12,000 items in this database. It has some duplicate data in it
that is making some problems. Is there some way to creat a table or a Query
that will tell me what the items are. I have tried to use the analyzer but
it keeps putting all 12,000 item in the table. I dont want to have to work
with all 12,00 parts only the ones with duplicates.

 

[Guest]

Try this SQL in a query to check for duplicates in specific field

SELECT TableName.FieldName, Count(TableName.FieldName) AS CountOfFieldName
FROM TableName
GROUP BY TableName.FieldName
HAVING Count(TableName.FieldName)>1

 

[George Nicholson]

If you select Queries in the database window and then "New", one of the
options should be "Find Duplicates Query Wizard".

It is possible that this wizard wasn't installed. If that's the case, can
you do a re-install/repair of Office/Access?

If not, you want to construct a query something like the following.
This example searches datAssets for duplicate values in the field
Description and returns Description and AssetID (you can add as many fields
from the table as you want).

SELECT Description, AssetID
FROM datAssets
WHERE (((Description) In (SELECT [Description] FROM [datAssets] As Tmp GROUP
BY [Description] HAVING Count(*)>1 )))
ORDER BY Description, AssetID;

HTH,

 

[Guest]

You can use the Find Duplicates Query - go to Queries, click New and choose
the Find Duplicates Wizard then just follow the prompts.