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.
- 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];
-Stewart
92
