[AccessD] Operation must use an updatable query

Thank you Charlotte for weighing in!

Yes, I get the same number using INNER, because the second table has so much
redundancy at the level I am joining (Site_DB).

Thank you.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Wednesday, June 08, 2011 3:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Operation must use an updatable query

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