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
Tidak ada komentar:
Posting Komentar