William Benson (VBACreations.Com)
vbacreations at gmail.com
Thu Jun 9 10:41:48 CDT 2011
I want to make the target table more readable as a standalone - for data verification purposes, and also because I would sort of like to capture what Table2's data had looked like at a point in time (if desired) rather than have to store an archive of all the records of table2. Yes, I know there are other ways around this. Anyway, consider it an intellectual exercise which failed :-). I do not expect anyone needs to weigh in on more solutions, I think the only thing I would be able to do is a temp table -- which would bloat my database somewhat, since there are more than one table I am updating from, all large... but I think if I used delete and append queries to build the temp tables, the bloat would be a one-time hit. (Unless the person cleared the temp tables and compacted, then I suppose the database would bulge and shrink repeatedly). Or - I could live with the redundant updating, which is not really a problem - something like a 0.01 second difference and I've already chewed up many minutes of everyone's time worrying about it. Anyway, in answer to your first question Charlotte, a simple example. Table1 might have 123 Null Null 456 Null Null 789 Null Null Table2 Might have 123 Bill Benson Doctor A 456 Amy Peterson Blacksmith A 456 Amy Peterson Blacksmith B 777 Charlie Franklin Baseball Player F 777 Charlie Franklin Baseball Player G 777 Charlie Franklin Baseball Player H 789 Mike Conway Bricklayer M 789 Mike Conway Bricklayer N 123 Bill Benson Doctor And I want to update Table1 so it would be 123 Bill Benson 456 Amy Peterson 789 Mike Conway When I try an updatable query without grouping the Table2 items, I get far more rows Access "says" are being updated than simply 3. This is made up date and not my real table, so I don't know the exact number of rows Access would say in the case of Table1 and Table2 without actually running this test. But by grouping in *some* way ... maybe with First, or Last, I had hoped to convert Table2 into a subset without the third column -- like 123 Bill Benson Doctor 456 Amy Peterson Blacksmith 777 Charlie Franklin Baseball Player 789 Mike Conway Bricklayer Then the join on the numeric first column (which is not PK nor unique in Table2) would enable me to Update only 3 rows in Table 1 with the person's name and Occupation I hope this is clear, 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 10:51 PM To: Access Developers discussion and problem solving Subject: Re: [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 > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com