James-
Is there an index on the PhoneNumber and StateRepresentativeDistrict?
Note that the query as you have constructed it will only flag records where there are multiples for the same PhoneNumber AND district. If you just want to flag records that have more than one record for the same phone number, remove the WHERE clause from the subquery.
Per our previous discussion, you really want to flag only the records where the phone number appears more than once but the districts are different. Your query as stated won't do that. Removing the WHERE clause will cause it to run much faster, but it will flag all records where the phone number appears in more than one record. You could subsequently make another pass to "unflag" the records that all have the same district.
Query1:
UPDATE Registrations SET Registrations.xDupPhoneDistrict = 1
WHERE (((Registrations.PhoneNumber) In (SELECT [PhoneNumber] FROM [Registrations] As Tmp2
GROUP BY [PhoneNumber] HAVING Count(*)>1 )));
Query2:
UPDATE Registrations SET Registrations.xDupPhoneDistrict = 0
WHERE Registrations.xDupPhoneDistrict = 1
AND NOT EXISTS
(SELECT * FROM Registrations As T2
WHERE T2.PhoneNumber = Registrations.PhoneNumber
AND T2.StateRepresentativeDistrict <> Registrations.StateRepresentativeDistrict)
The first query flags all records where the same phone number exists in another record.
The second query unflags any flagged record where no other record exists that has a district different from the district in the current record.
That should leave you with duplicate phone number records flagged where the districts aren't the same.
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
(Paris, France)
On Aug 5, 2014, at 10:15 AM, James McAlister kc5qeg@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I'm trying to run this update query on a 1.6 million record table, but it executes way too slowly. I let it run overnight for about 7 hours, and this morning the progress bar indicated that it was only about 25% done. I know that subqueries are slow, but this just won't work. I'm trying to flag records where phone numbers AND legislative district are duplicated.
Is there any other construct or approach I can use to update the field in a reasonable time? Using Access 2010 on Windows 7 Pro.
Thanks!!!
——
UPDATE Registrations SET Registrations.xDupPhoneDistrict = 1
WHERE (((Registrations.PhoneNumber) In (SELECT [PhoneNumber] FROM [Registrations] As Tmp2 WHERE Tmp2.PhoneNumber = Registrations.PhoneNumber AND Tmp2.StateRepresentativeDistrict = Registrations.StateRepresentativeDistrict GROUP BY [PhoneNumber] HAVING Count(*)>1 )));
——
__._,_.___
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) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar