What would be faster than doing a Loop comparison?

Panorama X
1

Looking for some creative minds.

I have about 70,000 records with each containing a Cust_ID field which should be unique for every customer. Unfortunately, some customers have been assigned an identical ID to another customer. Given that the data is correct, in the image below, I can see that there is a record with Cust_ID 12345 that has a different birth date from the other records of the same Cust_ID. This tells me that I have 2 customers with the Cust_ID of 12345.

What would be an efficient way to identify every Cust_ID that has multiple Birthdates (indicating multiple customers with the same Cust_ID)? It is correct that I would have multiple records of the identical customer.

(I did write a procedure that did a Group, then a comparison of birthdate values but with 70,000 records, it is probably not the right way to be doing this.)

Duplicate ID
Duplicate ID258×160 2.98 KB

Ideas?

2

How about this. Give your database an extra date field, and then do this

Field Cust_ID
GroupUp
Field BirthDate
Minimum
Field Xtra
FormulaFill BirthDate
Field BirthDate
Maximum
Select BirthDate≠Xtra

That should select the summary records where the minimum and maximum birthdates in the group don’t match.

3

Looks good to me.

Thank you David. Thinking outside the box just wasn’t working for me as quickly as I needed but your creativity was great.