Updating one table from another

[leighb]

I want to add a lot of e-mail addresses to a membership database.
The database has 2 relevant tables

Home details
with fields Group ID: various Address fields: E-mail

Personal Details
with fields GroupID: FirstName: Surname: etc etc

These two are linked by the GroupID field

I have another table with fields FirstName: Surname: E-Mail

I would like to be able to design a query that checks in the Personal Details table for match with FirstName and Surname, and then updates the E-Mail field in the Home Details table.

Is this possible, and how should the query be designed?

Thanks
Leigh

 

[Kinger43]

I take it the GroupID field is not unique to each individual record? If this is the case you need to find another field that you can use to link Home Details and Personal Details.

 

[gemma-the-husky]

open a design window, put all 3 tables in

now link surname/forename together - grab and drag, and then link groupid together (this may beautomatic if you have a relationship set)

now you can change the query to an update query, and update whichever fields you like - this may give strange results if you have duplicated names in one of the personal details tables

 

[leighb]

open a design window, put all 3 tables in

now link surname/forename together - grab and drag,

and then link groupid together
GroupID are linked automatically,

now you can change the query to an update query, and update whichever fields you like - this may give strange results if you have duplicated names in one of the personal details tables

I am not sure I understand the above, do you mean link them separately in each table they appear, or is there some way to link them so they must appear together?

Obviously we have lots of Johns and lots of Smiths, but not too many John Smiths (although maybe some )