Update Query Help

[eoceguera]

here's my problem, hope someone can help me.. thanks

Table 1
name , lastname , ID, address , phone
jack , inthebox , , aaaaaaaa, 1234567
mickey , mouse , , ssssssss, 1234567

Table 2
name , lastname, ID
jack , inthebox, 1
mickey , mouse , 2

i need an update query where the filed ID form table 1 will be pupulated from the ID field of table 2
for this example assuming that the name field is the key and it will only have those records no more..

i need to run the query.. and have this result.

Table 1
name , lastname , ID, address , phone
jack , inthebox , 1, aaaaaaaa, 1234567
mickey , mouse , 2, ssssssss, 1234567

what do you think?? can it be done??

thanks

 

[Guest_imported]

in the update query design view, create a join between the name from table 1 to name in table 2 and create a join between the name from table 1 to name in table 2, then run the query to update the field id in table 1 with data from table 2

update to criteria for the field in table 1:
[Table2].[field]

assuming all the name and last names in both tables are equal, the field id in table 2 will be updated into table 1

 

[eoceguera]

Thanks alot.. i tryed it.. it worked.. on the update side.. the only problem is that table 1 now has dupplicate records..

any other suggestions?

 

[THoey]

The update query should not have added duplicates to the table. I am think that data was there before you ran the update. I would create a copy of the table structure, set the ID field unique not null, and then run an append query that based off SELECT * from TBL1. This will only allow a single record (no dupes). After that, you can delete the table with the dupes and then rename the new table to your old table name. Terry M. Hoey