[MS_AccessPros] Removing unwanted results from a duplicates query

 

Hello, Experts!

I'm using the following query (created with the query wizard) to locate duplicated phone numbers in my voter file, and it appears to do that properly, producing sample results similar to those shown below.

SELECT Registrations.PhoneNumber, Registrations.StateRepresentativeDistrict

FROM Registrations

WHERE (((Registrations.PhoneNumber) In (SELECT [PhoneNumber] FROM [Registrations] as Tmp GROUP BY [PhoneNumber] HAVING Count(*)>1 )))

ORDER BY Registrations.PhoneNumber, Registrations.StateRepresentativeDistrict;

PhoneNumber     StateRepresentativeDistrict

2031234567          058

2031234567          058

2035678901          099

2035678901          100

2030123456          024

2030123456          024

2030123456          024

2030123456          093

2030123456          093

What I need to do now is to refine these results as follows:

1. Exclude all instances where StateRepresentativeDistrict is the same for ALL duplicated results. For example, 2031234567 is duplicated twice but for the same district 058, so both instances should be excluded.

3. On the other hand, 2035678901 is duplicated twice but for different districts, 099 and 100, so both should remain.

2. Finally, 2030123456 is duplicated five times, three for district 024 and twice for district 093. I want to show just one instance for district 024 and one for 093, excluding the two extras for district 024 and the one extra for 093.

I hope this make sense and would appreciate any pointers on how to implement.

Running Access 2010 on Windows 7 Pro.

Thanks!

——

James

www.james-mc.com

Words To Live By

__._,_.___

---

Posted by: James McAlister <kc5qeg@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___