How to find duplicated records?

[itlearner]

Hi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.

Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb

ID Trading Name Location No Location Street Location Suburb
41350 Arabica Café Grey St
263 Arabica Cafe` 1 GREY STREET
41294 Arashi Japanese Resturant 41 Courtenay PL
150 Arashi Kushi Yaki Bar 41 COURTENARY PLACE



From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.

SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];

 

[neileg]

Your use of the word accurate is a bit misleading. What you really want is to find similar records, Access is already finding exact matches. There's nothing built in to Access to do this in a fully automated way. You could use queries that extract the first few characters of a field and match on that, but there's still going to be a lot of human intervention to make this work.

 

[ejstefl]

What you want is to use a "Fuzzy" search, which Neil is right - Access does not do. However, if you search this database and the internet for Fuzzy Logic or Fuzzy Matching, you might find some interesting info.

You can start with this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=80556&highlight=fuzzy