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

James-

OK. "Date of the next election" can change, but not as frequently. Would he
know the difference if you gave him a query to open instead of the table? He
can certainly export a query to Excel.

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 5:29 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Why won't this query run?

I know it's bad form, but the client wants one big table with all the data
in it as he is a spreadsheet person and know little about Access. There are
no reports that are ever run, but he will sometimes select a subset (for a
legislative district, for example) and then export that to Excel for his
further sorting and analysis. The age is based on the date of the next
general election so only changes once a year. The data supplied by the
secretary of state is in one file with about 175 fields. Much of that (the
voting history) could actually be normalized. But he is happy with it as is
as long as we don't get constrained by file size. But if he ever wants to
streamline and optimize, then I'll be back with more questions! In the
meantime, I just do what he asks!

And I do appreciate all of the advice, even on such a tedious and
sub-optimal project!

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

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

> **
>
>
> James-
>
> Yup, you could do that. But why aren't you calculating the person's age
> dynamically in a query as of the date you create a report, etc? It's bad
> form
> to store a calculated value like that in a table, particularly since the
> value
> changes tomorrow!
>
>
> 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 4:53 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Why won't this query run?
>
> I see what you mean. The only potential difficulty that I see is that there
> are several fields (such as Age, which is calculated from the birth date)
> which I have added to the "old" at the client's request and are not in the
> "new" (raw data from the secretary of state). Now, after updating and
> appending from "old" to "new" I go back and update those extra few fields
> as required.
>
> James
> www.james-mc.com
> Words To Live By
>
> On Wed, Nov 30, 2011 at 8:52 AM, John Viescas <john@viescas.com> wrote:
>
> > **
> >
> >
> > 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
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

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

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

Yahoo! Groups Links