One table, dupe address, diff owners

Pat Hartman said:

Have you found the "find duplicates" query wizard? It will build the query for you if you are unfamiliar with SQl.

Click to expand...

plog said:

Why aren't the records with the address '1341 HALL ST' in your expected results?

Click to expand...

plog said:

So, this is just the method we discussed back in my first post. You will need 2 subqueries to get the data you want, here's the SQL for the first:

Code:

SELECT Sample_HSU_DB.Both_Name, Sample_HSU_DB.Both_addr
FROM Sample_HSU_DB
GROUP BY Sample_HSU_DB.Both_Name, Sample_HSU_DB.Both_addr;

Paste that into a new query object and name it '_sub1'. It gets all the unique name/addr permuations in your data. Next use this SQL:

Code:

SELECT [_sub1].Both_addr
FROM _sub1
GROUP BY [_sub1].Both_addr
HAVING (((Count([_sub1].Both_Name))>1));

Paste that into a new query with the name '_sub2'. It determines all the addr values that have multiple distinct name values. Finally, to get your data use this SQL:

Code:

SELECT Sample_HSU_DB.*
FROM _sub2 INNER JOIN Sample_HSU_DB ON [_sub2].Both_addr = Sample_HSU_DB.Both_addr;

Click to expand...

plog said:

Yes they are 3 seperate queries. You need to create each one as instructed, then run the last one to retrieve the data you want.

Click to expand...