update some values in one table from another table

[Lange]

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...

 

[John Vinson]

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]

 

[Lange]

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

 

[John Vinson]

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]

 

[Lange]

Thanks John,

Yes the references are unique, I will try this on Monday when I get back to
work.