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

James-

Because you're copying all the data from the "new" to the "old", the faster
method would be to import the "new" and then use an outer join from "old" to
"new" to Append (INSERT) any rows that were in old that are not now in "new".
That should run in just a matter of a few minutes.

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: Wednesday, November 30, 2011 3:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Why won't this query run?

If I have time I'll try what you suggest in updating matching records in
the "old" data and then appending the rows for the "new" data. I already
have queries for that but was just wanting to do it in one step since you
told me some time ago that the outer join in Access would also append new
records.

And about the speed. I'm actually doing this with Windows 7 running in a
virtual machine on my MacBook Pro and thought that might be slowing things
down. But apparently it's performing within reasonable expectations, so
I'll quit whining and remember that I'm working with 1.6 million records on
each side of the join. :)

Thanks for all the help!

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

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

> **
>
>
> James-
>
> Yes. The "benchmark" for performing an update of this sort on a full-blown
> database server (like SQL Server or Oracle) is 1,000 transactions a
> second. I'm
> actually surprised that a simple desktop database like Access can do 300 a
> second! Keep in mind that the way you have structured the query, you are
> asking
> Access to update every row in the existing table. It might be faster to
> load
> the new data and then append any not found in the old data.
>
>
> 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: Wednesday, November 30, 2011 2:54 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: 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
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links