update some values in one table from another table Lange 13.07.2006 19:47:01 Hi, I hope some one can help me, I have two tables containing stock information I need to update the information in one table from the other, but the tables contain some different products; Table1 just contains a product reference and quantity for about 2000 products; Table2 contains about 20 fields of data for about 500 products including product reference and quantity for each. Most of the products in Table2 have the same product reference that can be found in Table1, but some are different. What I would like to do is update the stock information in Table2 using the information in Table1, with any records in Table2 which cannot be found in Table1 left as they are i.e. not updated. When I try to do this with my limited skills creating a query, the table produced only contains values for product fererences which are in both tables. Can anyone help? I can clarify any details if need be. ....Thanks...

Re: update some values in one table from another table John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 13.07.2006 22:34:14 On Thu, 13 Jul 2006 12:47:01 -0700, Lange <Lange[ at ]discussions.microsoft.com> wrote: [Quoted Text] >Hi, I hope some one can help me, > >I have two tables containing stock information I need to update the >information in one table from the other, but the tables contain some >different products; > >Table1 just contains a product reference and quantity for about 2000 products; >Table2 contains about 20 fields of data for about 500 products including >product reference and quantity for each. > >Most of the products in Table2 have the same product reference that can be >found in Table1, but some are different. > >What I would like to do is update the stock information in Table2 using the >information in Table1, with any records in Table2 which cannot be found in >Table1 left as they are i.e. not updated. When I try to do this with my >limited skills creating a query, the table produced only contains values for >product fererences which are in both tables. > >Can anyone help? I can clarify any details if need be. ....Thanks... I'm not sure I understand. If you have records in Table1 with different references than the references in Table2, how can Access (or any program or person!) identify which record needs to be updated? What are these "product references" and what should constitute a match, for "the same product", if the value in the reference field is different? John W. Vinson[MVP]

Re: update some values in one table from another table Lange 14.07.2006 05:39:01 Sorry, I'v obviously not been clear enough, here is an example. Table 1 Table 2 Reference Stock Reference Stock ( Plus 20 more fields) Ref001 10 ref002 0 ref002 10 ref003 0 ref003 11 refA 0 ref004 02 ref005 0 ref005 07 refB 0 ref006 12 etc.. ref006 13 etc... Now I'm not woried about the entries in Table2 where the references don't match those in Table1 (shown as refA abd refB, but I would like to update those that do match, so my resulting table will look like.... Table 3 Reference Stock ref002 10 ref003 11 refA 0 ref005 07 refB 0 etc.. Any clearer

Re: update some values in one table from another table John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 14.07.2006 17:36:22 On Thu, 13 Jul 2006 22:39:01 -0700, Lange <Lange[ at ]discussions.microsoft.com> wrote: [Quoted Text] >Sorry, I'v obviously not been clear enough, here is an example. > >Table 1 Table 2 >Reference Stock Reference Stock ( Plus 20 >more fields) >Ref001 10 ref002 0 >ref002 10 ref003 0 >ref003 11 refA 0 >ref004 02 ref005 0 >ref005 07 refB 0 >ref006 12 etc.. >ref006 13 >etc... > >Now I'm not woried about the entries in Table2 where the references don't >match those in Table1 (shown as refA abd refB, but I would like to update >those that do match, so my resulting table will look like.... > Table 3 >Reference Stock >ref002 10 >ref003 11 >refA 0 >ref005 07 >refB 0 >etc.. >Any clearer Be sure that the Reference field is the Primary Key of table1, or that it at least has a unique Index. (If you have two records in Table1 both with ref006, as in your example, you're in trouble - how, again, can Access identify WHICH of them you want to update?) If you do have this unique reference, create a new Query adding Table1 and Table2. Join the two tables by Reference (if Access doesn't do so automatically). This will join the record for Ref002 to the record for Ref002 in the second table, and so on - unmatched references in Table2 won't matter. Change the query to an Update query and put =[table 2].[Stock] on the Update To line under Stock. Note that if there are multiple records for Ref002 in Table2, you still will have a problem - *which one of them* is the correct new value? John W. Vinson[MVP]

Re: update some values in one table from another table Lange 15.07.2006 07:15:02 Thanks John, Yes the references are unique, I will try this on Monday when I get back to work.