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

I'll bet that's it also. If not, I'll be back!

Thanks!!

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
>
>
>