Duplicate value

[voroojak]

Hi
I have a problem with duplicate values.
in my table i have some customer that have more than 1 id. i can fine them with find duplicate values. but the problem is that i want to find those customer that one of their id is started with 2233 and the other one is started with 2234.
but with "Find Duplicate Query Wizard" i will find any customer that has more than one record. i just want those customer that one of the id is tsrtaed with 2233 and the other one is 2234.

i would appreciate any suggestion.

thanks

[Stewart Ross]

Trying to find customers with multiple IDs is a bit tricky. You need to identify them from non-unique alternative fields, and some manual filtering may be required to sieve out false positive matches.

Create a new query into which you insert TWO copies of your customer table. Access will alias the second copy as customer_1 or similar to distinguish it from the first copy.

Join the two copies of the customer table on the customer forename and surname fields, and add the name fields, ID and any others you want to check from one of the two tables. Order the query by surname and Forename.

In one of the criteria fields for the unique customer ID field place the following criterion:
<> [customer_1].[customer ID]
(replacing the table and field names as appropriate for your situation).

Save the query and run it. It will return all rows where the names match but the IDs do not.

Example SQL for the query is listed below.

[code=sql]SELECT [Customer].[Customer ID], [Customer].[Surname], [Customer].[Forename]
FROM [Customer] INNER JOIN [Customer] AS [Customer_copy]
ON ([Customer].[Forename] = [Customer_copy].[Forename]) AND ([Customer].[Surname] = [Customer_copy].[Surname])
WHERE ([Customer].[Customer ID)<>[Customer_copy].[Customer ID])
ORDER BY [Customer].[Surname], [Customer].[Forename];[/code]
-Stewart

[voroojak]

Hi
thanks alot for your solution. i think i understood, what to do.
thank you 100000000000000 000000000000000 00000 times