Adding data from 1 table to another table

[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:- GroupID: various Address fields: E-mail

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

These two are linked by the GroupID field

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

I would like to be able to design a query that checks 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

 

[John W. Vinson]

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

Home details
with fields:- GroupID: various Address fields: E-mail

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

These two are linked by the GroupID field

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

I would like to be able to design a query that checks 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

Sure, it's possible. is it a good idea? Probably not, for several reasons!

What if you have Bill Jones, Bill Jones, Bill's son Bill Jones Jr., and
another Bill Jones in your Personal Details table? Who gets which email
address?

Why would you want to store the name and email address redundantly in a second
table anyway?

Just what are you trying to accomplish by doing this??


To answer though: create a query joining your new table to Personal Details,
joining FirstName to FirstName, Surname to Surname. Then add Home Details
joining PersonalDetails.GroupID to HomeDetails.GroupID. You'll need a unique
Index on the combination of FirstName and Surname (which means that you can't
have ANY duplicate names in the entire table) for this to be updateable. If it
is, though, you can update Home Details.email to the value of yourtable.email.

 

[LeighB]

John,
Thank you I will try that.

It may seem an odd thing to want to do, but we are trying to get a load of
our members to give us their e-mail addresses, and we need to enter them in
the database.

I was hoping that rather than going through several hundred records adding
the appropriate address to the record we could make a new table with just
FirstName SurName and e-mail and then merge that into the relevant table.

If it works it could save a lot of time on our office PC as the table could
be created elsewhere and then imported and merged.

 

[LeighB]

John,

A further question, you say "You'll need a unique

Index on the combination of FirstName and Surname" do you mean in the Personal Details table, the new one or both?

How do I do it?

I have very few duplicates, and if I add the "Title" field then there are
none as Dr or Mr makes each member unique.

 

[John W. Vinson]

John,

A further question, you say "You'll need a unique

Open the Personal Details table in design view. Click the "Indexes" tool in
the toolbar - it looks like lightning hitting a datasheet.

Type some distinctive name in the left column - UniquePerson say - and put
FirstName in the second column. On the next row down leave the first column
blank and type Surname in the second, and so on for up to ten fields.

How do I do it?

I have very few duplicates, and if I add the "Title" field then there are
none as Dr or Mr makes each member unique.

I once worked with Dr. Lawrence David Wise, Ph.D. and his colleague Dr.
Lawrence David Wise, Ph.D. Larry was tall, blond and outgoing; L. David was
stocky, dark and taciturn.

 

[LeighB]

John,
Many thanks, I have it working with a small amount of test data, so no
reason why it should not work with larger numbers.

I take your point about your 2 colleagues, but I know that Title plus
Forename plus Surname equals unique individuals in our membership database.

I now have a system that can minimise the work involved in updating our
membership lists.

Thanks again.