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