ACCESS FEED: Re: [MS_AccessPros] Why won't this query run?

This worked quite well with my small (100 record) Import table. Fully
populated, however, the Import table has about 1.6 million records, as does
the Registrations table. The query is running exceedingly slow (looks like
it may take hours), and I'm wondering if that is because of the left outer
join? Would it be more efficient (faster) to do this in two steps: 1)
append new records and 2) then run a regular update query?

This is Access 2010 on Windows 7 Pro with 4GB RAM, so I was expecting it to
move right along. I've rebooted and have had a look at the CPU usage in
Task Manager thinking it would be maxed out, but it's 5% or less.

Thanks for any ideas!

James
www.james-mc.com
Words To Live By

On Tue, Nov 29, 2011 at 11:02 AM, James McAlister <forums01@james-mc.com>wrote:

> I'll bet that's it also. If not, I'll be back!
>
>
> Thanks!!
>
> James
> www.james-mc.com
> Words To Live By
>
>
> On Tue, Nov 29, 2011 at 10:58 AM, John Viescas <john@viescas.com> wrote:
>
>> **
>>
>>
>> James-
>>
>> If TEXT_REGISTRANT_ID is the Primary Key of Registrations, then you MUST
>> set it
>> in the SET clause. If there are records in Import that have a VoterID
>> that's
>> not found in TEXT_REGISTRANT_ID, Access will attempt to add the records
>> for you,
>> but only if you set ALL the required fields in Registrations. I suspect
>> that's
>> the problem.
>>
>> John Viescas, author
>> Microsoft Office Access 2010 Inside Out
>> Microsoft Office Access 2007 Inside Out
>> Building Microsoft Access Applications
>> Microsoft Office Access 2003 Inside Out
>> SQL Queries for Mere Mortals
>> http://www.viescas.com/
>> (Paris, France)
>>
>>
>> -----Original Message-----
>> From: MS_Access_Professionals@yahoogroups.com
>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of James
>> McAlister
>> Sent: Tuesday, November 29, 2011 5:30 PM
>> To: MS_Access_Professionals@yahoogroups.com
>> Subject: [MS_AccessPros] Why won't this query run?
>>
>> In trying to execute the following query, I get an error: "Microsoft
>> Access can't update all records in the update query." Then follows a
>> statement that 16 records can't be updated due to key violations.
>>
>> At first I thought that there might be nulls in either Import.VoterID
>> or Registrations.TEXT_REGISTRANT_ID, but that's not the case. I do
>> have a field Import.TEXT_REGISTRANT_ID, however, which has the same
>> number of nulls as the number of records that won't update--and they
>> are the same records that are missing when I view the query in
>> datasheet view.
>>
>> So how is this happening since Import.TEXT_REGISTRANT_ID doesn't
>> factor into the query? Am I missing a table setting somewhere?
>>
>> Just for background, my past work with these voter files has always
>> utilized Import.TEXT_REGISTRANT_ID and
>> Registrations.TEXT_REGISTRANT_ID as the join fields (as they
>> consistently contained the unique voter ID) in my queries. This time,
>> however, whoever prepared the files I need to import at the secretary
>> of state's office added a new field, VoterID, with the proper voter ID
>> number in it but left the old TEXT_REGISTRANT_ID that had nulls for
>> voters with no actual vote history. But why should that be a problem
>> if I'm not using Import.TEXT_REGISTRANT_ID in the query?
>>
>> By the way, Import is a linked table in another Access file into which
>> I have imported the voter registration data. I'm testing this on a
>> small file of 100 imported records (16 of which don't import), but the
>> real file has about 1.5 million records with about 300,000 that don't
>> import.
>>
>> I hope this is clear. There's something basic that I'm missing or
>> don't understand and would appreciate any insight.
>>
>> UPDATE Import AS I LEFT JOIN Registrations AS R ON I.VoterID =
>> R.TEXT_REGISTRANT_ID SET R.County = [I].[County],
>> R.CDE_REGISTRANT_STATUS = [I].[CDE_REGISTRANT_STATUS],
>> R.CDE_REGISTRANT_REASON = [I].[CDE_REGISTRANT_REASON], R.date_of_birth
>> = [I].[date_of_birth], R.date_of_registration =
>> [I].[date_of_registration], R.TEXT_NAME_LAST = [I].[TEXT_NAME_LAST],
>> R.TEXT_NAME_FIRST = [I].[TEXT_NAME_FIRST], R.TEXT_NAME_MIDDLE =
>> [I].[TEXT_NAME_MIDDLE], R.CDE_NAME_SUFFIX = [I].[CDE_NAME_SUFFIX],
>> R.TEXT_RES_ADDRESS_NBR = [I].[TEXT_RES_ADDRESS_NBR],
>> R.TEXT_RES_ADDRESS_NBR_SUFFIX = [I].[TEXT_RES_ADDRESS_NBR_SUFFIX],
>> R.CDE_STREET_DIR_PREFIX = [I].[CDE_STREET_DIR_PREFIX],
>> R.TEXT_STREET_NAME = [I].[TEXT_STREET_NAME], R.DESC_STREET_TYPE =
>> [I].[DESC_STREET_TYPE], R.CDE_STREET_DIR_SUFFIX =
>> [I].[CDE_STREET_DIR_SUFFIX], R.DESC_UNIT_TYPE = [I].[DESC_UNIT_TYPE],
>> R.TEXT_RES_CITY = [I].[TEXT_RES_CITY], R.CDE_RES_STATE =
>> [I].[CDE_RES_STATE], R.TEXT_RES_ZIP5 = [I].[TEXT_RES_ZIP5],
>> R.TEXT_RES_ZIP4 = [I].[TEXT_RES_ZIP4], R.IND_MAIL = [I].[IND_MAIL],
>> R.PrecinctName = [I].[PrecinctName], R.PrecinctDesignationCode =
>> [I].[PrecinctDesignationCode], R.PrecinctPartName =
>> [I].[PrecinctPartName], R.PrecinctPartDesignationCode =
>> [I].[PrecinctPartDesignationCode], R.CongressionalDistrict =
>> [I].[CongressionalDistrict], R.StateSenateDistrict =
>> [I].[StateSenateDistrict], R.StateRepresentativeDistrict =
>> [I].[StateRepresentativeDistrict], R.CourtOfAppealsDistrict =
>> [I].[CourtOfAppealsDistrict], R.JudicialDistrict =
>> [I].[JudicialDistrict], R.DistrictCourtDistrict =
>> [I].[DistrictCourtDistrict], R.TownshipDistrict =
>> [I].[TownshipDistrict], R.JusticeOfThePeaceDistrict =
>> [I].[JusticeOfThePeaceDistrict], R.SchoolDistrict =
>> [I].[SchoolDistrict], R.SchoolDistrictZone = [I].[SchoolDistrictZone],
>> R.MunicipalityDistrict = [I].[MunicipalityDistrict], R.WardDistrict =
>> [I].[WardDistrict], R.DateLastVoted = [I].[DateLastVoted],
>> R.Primary2002 = [I].[Primary2002], R.Primary2002CountyVotedIn =
>> [I].[Primary2002CountyVotedIn], R.Primary2002PartyVoted =
>> [I].[Primary2002PartyVoted], R.Primary2002HowVoted =
>> [I].[Primary2002HowVoted], R.PrimaryRunoff2002 =
>> [I].[PrimaryRunoff2002], R.PrimaryRunoff2002CountyVotedIn =
>> [I].[PrimaryRunoff2002CountyVotedIn], R.PrimaryRunoff2002PartyVoted =
>> [I].[PrimaryRunoff2002PartyVoted], R.PrimaryRunoff2002HowVoted =
>> [I].[PrimaryRunoff2002HowVoted], R.General2002 = [I].[General2002],
>> R.General2002CountyVotedIn = [I].[General2002CountyVotedIn],
>> R.General2002PartyVoted = [I].[General2002PartyVoted],
>> R.General2002HowVoted = [I].[General2002HowVoted], R.Primary2004 =
>> [I].[Primary2004], R.Primary2004CountyVotedIn =
>> [I].[Primary2004CountyVotedIn], R.Primary2004PartyVoted =
>> [I].[Primary2004PartyVoted], R.Primary2004HowVoted =
>> [I].[Primary2004HowVoted], R.PrimaryRunoff2004 =
>> [I].[PrimaryRunoff2004], R.PrimaryRunoff2004CountyVotedIn =
>> [I].[PrimaryRunoff2004CountyVotedIn], R.PrimaryRunoff2004PartyVoted =
>> [I].[PrimaryRunoff2004PartyVoted], R.PrimaryRunoff2004HowVoted =
>> [I].[PrimaryRunoff2004HowVoted], R.General2004 = [I].[General2004],
>> R.General2004CountyVotedIn = [I].[General2004CountyVotedIn],
>> R.General2004PartyVoted = [I].[General2004PartyVoted],
>> R.General2004HowVoted = [I].[General2004HowVoted], R.Primary2006 =
>> [I].[Primary2006], R.Primary2006CountyVotedIn =
>> [I].[Primary2006CountyVotedIn], R.Primary2006PartyVoted =
>> [I].[Primary2006PartyVoted], R.Primary2006HowVoted =
>> [I].[Primary2006HowVoted], R.PrimaryRunoff2006 =
>> [I].[PrimaryRunoff2006], R.PrimaryRunoff2006CountyVotedIn =
>> [I].[PrimaryRunoff2006CountyVotedIn], R.PrimaryRunoff2006PartyVoted =
>> [I].[PrimaryRunoff2006PartyVoted], R.PrimaryRunoff2006HowVoted =
>> [I].[PrimaryRunoff2006HowVoted], R.General2006 = [I].[General2006],
>> R.General2006CountyVotedIn = [I].[General2006CountyVotedIn],
>> R.General2006PartyVoted = [I].[General2006PartyVoted],
>> R.General2006HowVoted = [I].[General2006HowVoted], R.PresPrimary2008 =
>> [I].[PresPrimary2008], R.PresPrimary2008CountyVotedIn =
>> [I].[PresPrimary2008CountyVotedIn], R.PresPrimary2008PartyVoted =
>> [I].[PresPrimary2008PartyVoted], R.PresPrimary2008HowVoted =
>> [I].[PresPrimary2008HowVoted], R.Primary2008 = [I].[Primary2008],
>> R.Primary2008CountyVotedIn = [I].[Primary2008CountyVotedIn],
>> R.Primary2008PartyVoted = [I].[Primary2008PartyVoted],
>> R.Primary2008HowVoted = [I].[Primary2008HowVoted],
>> R.General2008CountyVotedIn = [I].[General2008CountyVotedIn],
>> R.General2008PartyVoted = [I].[General2008PartyVoted],
>> R.General2008HowVoted = [I].[General2008HowVoted], R.Primary2010 =
>> [I].[Primary2010], R.Primary2010CountyVotedIn =
>> [I].[Primary2010CountyVotedIn], R.Primary2010PartyVoted =
>> [I].[Primary2010PartyVoted], R.Primary2010HowVoted =
>> [I].[Primary2010HowVoted], R.PrimaryRunoff2010 =
>> [I].[PrimaryRunoff2010], R.PrimaryRunoff2010CountyVotedIn =
>> [I].[PrimaryRunoff2010CountyVotedIn], R.PrimaryRunoff2010PartyVoted =
>> [I].[PrimaryRunoff2010PartyVoted], R.PrimaryRunoff2010HowVoted =
>> [I].[PrimaryRunoff2010HowVoted], R.General2010 = [I].[General2010],
>> R.General2010CountyVotedIn = [I].[General2010CountyVotedIn],
>> R.General2010PartyVoted = [I].[General2010PartyVoted],
>> R.General2010HowVoted = [I].[General2010HowVoted];
>>
>> Thanks!!
>>
>> James
>> www.james-mc.com
>> Words To Live By
>>
>> ------------------------------------
>>
>> Yahoo! Groups Links
>>
>>
>>
>
>