Charlotte Foust
charlotte.foust at gmail.com
Wed Jun 8 14:00:32 CDT 2011
The rule is that a query with more than two tables joined is not updateable. The outer join may be the reason you're getting an inflated number of records. Have you tried using an inner join? Charlotte Foust On Wed, Jun 8, 2011 at 11:50 AM, William Benson (VBACreations.Com) < vbacreations at gmail.com> wrote: > I should wait for someone else to tell me whether this is resolveable but > let me point out that the Google research I have done says it is a common > problem with Access SQL... JOINS and Update queries do not get along. There > was some person saying that if there was a unique key being joined ON then > there was some way it could be made updatable. I am not able to achieve this > unique key status in the two joined sources, so I have given up. > > Below is the replacement query, without any grouping. What is interesting > (and strange) is that although there are only 1,117 records in the table to > be updated, I get a prompt-type message asking me if it ok to update >9,000 > records. (This was the reason I decided to try a group by, I knew there was > duplication in the second data source).. > > > So it begs the question, what is the update query doing, obviously a single > row is being updated more than one time. I am guessing that the information > in the other fields of EACH encounter of Tbl_Matched_Sites.GIB_SITE_DB = > TblSites.SITE_DB will be written, but only the information in the last > encounter will be left by the time the query runs...? > > UPDATE Tbl_Matched_Sites > > LEFT JOIN > > TblSites > > on Tbl_Matched_Sites.GIB_SITE_DB = TblSites.SITE_DB > > SET > > Tbl_Matched_Sites.GIB_Plant_Name= TblSites.Site_Station_Name, > Tbl_Matched_Sites.GIB_Phys_Addr_1 = TblSites.ADDRESS_LINE1, > Tbl_Matched_Sites.GIB_Phys_City = TblSites.CITY, > Tbl_Matched_Sites.GIB_Phys_zip = TblSites.STATE > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > >