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

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