Data subsets

[Guest]

I have a table of customers that contains around 300K records, of which some
customers will have two or three different account numbers.

I then have a subset of those customers in a different table that contains
around 6,000 customers with no dups. I need to identify which of the 6,000
customers do not have duplicate accounts in the 300K record set.

Any ideas, I know I can do a find dups query to identify the other accounts,
but this is not exactly what I am looking for.

 

[John Vinson]

I have a table of customers that contains around 300K records, of which some
customers will have two or three different account numbers.

I then have a subset of those customers in a different table that contains
around 6,000 customers with no dups. I need to identify which of the 6,000
customers do not have duplicate accounts in the 300K record set.

Any ideas, I know I can do a find dups query to identify the other accounts,
but this is not exactly what I am looking for.

Interesting challenge - especially to have it be reasonably efficient!

I'd try a two-query solution: first identify the singletons, then join
the other table. How can you (reliably, I wouldn't trust names or
addresses) identify which customer is "the same" in the two tables?

Just as a speculation:

qryUniqueCustomers

SELECT <whatever fields identify the customer>
FROM Customers
GROUP BY <whatever fields identify the customer>
HAVING Count(*) = 1;

Then create a query joining qryUniqueCustomers to your second table by
these fields.


John W. Vinson[MVP]