Asger Blond
ab-mi at post3.tele.dk
Wed Jun 8 10:59:40 CDT 2011
The field FirstOfSITE_DB on which you are joining the subquery doesn't appear in the field list of the subquery. Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af William Benson (VBACreations.Com) Sendt: 8. juni 2011 17:28 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] Datasheet Subform column width a unhappily resetting I am getting a message that "operation must use an updatable query" from attempting to run this SQL. The reason I decided to try to join on a query is that TblSites has tremendous duplication in it, and I would update based on the TblSites table and it would be reported that many times more rows in Tbl_Matched_Sites were about to be updated than in fact there even were in the table. So I thought I might be able to improve performance by changing the join to a group by query. Is there a way to get this to work? UPDATE Tbl_Matched_Sites INNER JOIN (Select FirstOf(Site_Station_Name) as FirstOf Site_Station_Name, First(Address_Line1) as FirstOfAddress_Line1, First(City) as FirstOfCity, First(State) as FirstOfState >From TblSites Group By Site_Station_Name, Address_Line1, city, State ) as MainInfo ON Tbl_Matched_Sites.GIB_SITE_DB = MainInfo.FirstOfSITE_DB SET Tbl_Matched_Sites.GIB_Phys_Addr_1 = MainInfo.[ FirstOfAddress_Line1], Tbl_Matched_Sites.GIB_Phys_City = MainInfo.[ FirstOfCity], Tbl_Matched_Sites.GIB_Phys_State = MainInfo.[ FirstOfState] -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com