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

John,

Well, if YOU think it's pretty speedy, I'll not touch it! Having little
experience with what it ought to do, I just thought it "felt" slow. But
your analysis puts it into perspective!

Thanks!

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

On Wed, Nov 30, 2011 at 1:31 AM, John Viescas <john@viescas.com> wrote:

> **
>
>
> James-
>
> Even if it took 90 minutes, that's pretty speedy! Assuming it took 90
> minutes,
> that's 300 rows being updated PER SECOND. That time sounds OK to me.
>
>
> 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 11:27 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: 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
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>