How to update table using Innor join

[Vinod]

Hi All,

Please suggest best solution for the following query.
I've two tables (table1,table2) with same structure having fields Name and
Phone No

Table1
---------
Name Phone No
Smith 14567247
Paul 12648453
Bert 17685234
Smith 12345689

Table2
---------
Name Phone No
Smith
Paul
Bert
Smith

I required a query which will update Phone No from Table1 to Table2 for
matching names. In this scenario I should get two different phone numbers for
smith as it in table1.

It would be apreciated if I can solution without adding new columns in both
table.

Please share your thoughts.

Advanced thanks,
Vinod

 

[Beetle]

First, if you don't add a unique key column to your table then Access
will have no way of differentiating one "Smith" from the other.

Second, why do you have the same data in two different tables? A
person's name and phone number should exist in one - and only one -
table in your application. Are you going to delete the first table after
you update the second table?

 

[Vinod]

Thanks Beetle for your response,

There are nearly 15 columns in my actual table. For better understanding
I've used this example. Actually it has to look for a product with based on
macthing product it has to retrieve the corresponding value to update in
another table.

In other words it is very closer to Vlookup formula in excel but here I'm
having duplicate values in lookup column but retrieving column will be
different for duplicated product. The retrieving column data may get
duplicated for another product.

Is there any solution for my requirement?

Advanced Thanks,
~Vins

 

[Larry Linson]

If you don't have something besides the name "Smith", it will be impossible
to determine which Smith record in Table 1 should match which Smith record
in Table 2. If you are about to say "the order of the records in the
table", please remember that in relational database tables, records are, by
definition, unordered.

I'm only expanding on what Beetle already told you, by the way.

Larry Linson
Microsoft Office Access MVP