Duplicate values - Postcode

[PaulJK]

I have a database of customers and I want to check for duplicate entries.

I have written a query that looks at duplicates of customer names. This works fine. The problem is that some users are entering new customers and may slightly change the wording of the customer name so that it therefore will not appear in this query.

What I am trying to write is a query which will look at postcodes and will identify if i have more than one customer ID (unique value) associated with a postcode.

I have managed to write a query with three entries:

CustAddPostcode
CompanyName
CustomerID

The criteria for 'CustAddPostcode' is: In (SELECT [CustAddPostcode] FROM [qryCustAddresses] As Tmp GROUP BY [CustAddPostcode] HAVING Count(*)>1 ).

An example of the results are:

EC2N 1RE Customer # 1 23
EC2N 1RE Customer # 1 23
EC2N 1RE Customer # 1 23
C034 4RE Customer # 3 555
C034 4RE Customer # 3 555
C034 4RE Customer # 3 555
C034 4RE Customer # 3 555
MC1 5RE Customer # 44 344
MC1 5RE Customer # 44 344

and so on.

This is not what i want as it will show for each postcode all of the entries, rather than Customer ID's postcodes with more than one different Customer ID.

Can anyone assist with this problem or suggest other options.

I thank you in advance.

Paul

 

[twoplustwo]

Could you poast a sample db please?

 

[PaulJK]

Thank you for your quick reply.

I already have some screen images. Do these help?

 

[twoplustwo]

Removed Company name from the query. Group by C_ID

 

[DCrake]

Simple Software Solutions

What happens if you have two or more customers who genuinely have the same post code?

Have you tried using a soundex search when the user wants to add a new customer?

Then again, what happens if a previously entered customer name is updated? are you checking again for duplicates?

The possiblities are endless... Who said that?

 

[PaulJK]

Thanks for the reply.

I have managed to do a Group By option on the first query which looks like it is working.

Thanks for your help.

 

[twoplustwo]

I mean that's a quick answer to your question...

Maybe have a think about DC's questions.