Gary Kjos
garykjos at gmail.com
Wed Jun 8 14:48:41 CDT 2011
Here is a link to a table of "when is a query update-able" that I saved a favorite to a while back http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx GK On Wed, Jun 8, 2011 at 2:33 PM, jwcolby <jwcolby at colbyconsulting.com> wrote: >> 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 >>> >>> >>> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com