[AccessD] Operation must use an updatable query

OK, you're trying to update one table from a second that has redundant
records?   Or is it the other way around?  If you do a join (inner or outer)
to a table wth a bunch of matches on the join key, you're going to get an
update for each one of those records, which is what sounds like is
happening.  The question I would have is what is the purpose of inserting
this information into the target table if you can already join to it?

Charlotte Foust

On Wed, Jun 8, 2011 at 12:19 PM, William Benson (VBACreations.Com) <
vbacreations at gmail.com> wrote:

> 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
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>