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

John, both fields do have indexes (no duplicates). The next time I have to
do this I may try doing the update part without the outer join to see if
that makes any difference in speed. I left the query running and ran some
errands and am guessing that it took 60 to 90 minutes.

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

On Tue, Nov 29, 2011 at 2:33 PM, John Viescas <john@viescas.com> wrote:

> **
>
>
> James-
>
> If there's an index on Registrations.TEXT_REGISTRANT_ID and on
> Import.VoterID
> (especially if one or both indexes is unique) it should run pretty quickly.
>
>
> 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 9:23 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: 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
> >>
> >>
> >>
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>