Using Data from one table to fill another

[JDB]

Inexperienced user here.

I have a table of contacts (name, address, etc.)

I want to create a second table that allows me to input each "contact" with
the contact.

In my mind, I would have a form with a lookup box that allows me to choose
the correct contact from table 1, populate appropriate fields in table 2, and
enter additional information in table 2.

If that sounds like a good plan, how would i go about doing it?

If not, what should I do instead?

thanks in advance for your help.

 

[Tom van Stiphout]

On Tue, 12 Aug 2008 15:33:44 -0700, JDB

You're making it too hard for yourself (and us) to talk about Contacts
and their Contacts.
What is it you really want to do?

-Tom.
Microsoft Access MVP

 

[JDB]

Yikes, I thought I was making it simple. I'll try again:

I have a table of 2,000 people. The fields are basic contact info - name,
address, title, etc.

A subset of the table (~400 and can be broken out in a query) needs to be
appended on a fairly routine basis. For example, I send one of them a
letter, I want to log it. Another calls me, I want to log it.

So, I want an easy way to enter the new information and create reports. The
reports parts I (think) i can handle...it is the "linking" (joining?
relating?) of the two table at which I fail.

Thanks for your help.

 

[John W. Vinson]

Yikes, I thought I was making it simple. I'll try again:

I have a table of 2,000 people. The fields are basic contact info - name,
address, title, etc.

A subset of the table (~400 and can be broken out in a query) needs to be
appended on a fairly routine basis. For example, I send one of them a
letter, I want to log it. Another calls me, I want to log it.

So, I want an easy way to enter the new information and create reports. The
reports parts I (think) i can handle...it is the "linking" (joining?
relating?) of the two table at which I fail.

STOP. You're misunderstanding how relational databases work!

The contact information should exist once, and once only, in the Contacts
table, noplace else.

If you have a subset and want to "pull it out", put a field in the contacts
table that identifies the person as being part of the subset (a yes/no field,
or some other datatype that you can maintain easily); use a Query to select on
the basis of this field.

For logging interactions with the contact, you need another table - NOT
containing names, or addressess, or the like; instead it would have a
ContactID, a link to the Primary Key of your current table. If your current
table doesn't have a primary key it needs one!!

You would then use a Form based on the Contacts table, with a Subform based on
the Interactions table to enter letters, phone calls, etc.; your Reports would
be based on a query joining the two tables.

 

[JDB]

OK. That makes sense and was one of the solutions I tried, btu was stymied
by not knowing how to link the Keys. The table does have a primary key...how
do I link the field int he second table to it?

Much appreciated

 

[John W. Vinson]

OK. That makes sense and was one of the solutions I tried, btu was stymied
by not knowing how to link the Keys. The table does have a primary key...how
do I link the field int he second table to it?

The first table's Primary Key - let's call it ContactID - might be an
autonumber or some other datatype field. I don't know, you haven't said. <g>

The second table should have a field of the same datatype and size - Long
Integer if the ContactID is Autonumber. It can be named anything, but let's
call it ContactID also, just for clarity. Use the Relationships window to
create a relationship from Contacts.ContactID to Interactions.ContactID, with
the "enforce referential integrity" checkbox checked.

When you create the Subform you'll be offered ContactID as the Master and
Child Link Field. Use it.