Ah! DISTINCT! I should have thought of that, John, but didn't! I'm running it now, but it's taking a really long time on the 1.6 million records that I'm dealing with. It's been going about 30 minutes now, and I don't know how much longer to expect.
I can see how DISTINCT will reduce the duplicated phone records for each phone number down to one, which is what I want—except for my first example below with two identical phone numbers, both for district 058. Won't DISTINCT leave one of those intact? I don't want to see any duplicated records unless the duplicated phone numbers have different districts.
——
James
www.james-mc.com
Words To Live By
From: John Viescas JohnV@msn.com [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com>
Reply: ms_access_professionals@yahoogroups.com <ms_access_professionals@yahoogroups.com>>
Date: August 4, 2014 at 4:47:00 PM
To: ms_access_professionals@yahoogroups.com <ms_access_professionals@yahoogroups.com>>
Subject: Re: [MS_AccessPros] Removing unwanted results from a duplicates query
James-
Try this:
SELECT DISTINCT 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;
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Aug 4, 2014, at 2:58 PM, James McAlister kc5qeg@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
• Privacy • Unsubscribe • Terms of Use
.
__._,_.___
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) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar