jwcolby
jwcolby at colbyconsulting.com
Wed Jun 8 14:33:02 CDT 2011
> The rule is that a query with more than two tables joined is not updateable. I'm glad I didn't know this because I have an updateable query with 12 tables in it, many of them outer joins. It is used as the query for a bound form. Damned ugly it is too! ;) And I do not recommend it. John W. Colby www.ColbyConsulting.com On 6/8/2011 3:00 PM, Charlotte Foust wrote: > 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 >> >> >>