Relationship Question

[NutritionHealer]

Hi, I can't believe how confused I am! I've spent hours and hours trying to
figure this out!

Here is the scenario:

I created 4 tables and matching forms. One has information from business
leads that I purchased (name, address, phone, email, etc) Another table has
info about my family and friends. Another has data from people who contacted
me that I did not previously know. So all of these tables contain basic
contact information common to all three, plus each table has info that is
different from all the others.

The last form and table I wanted to be a contact list of "all the people" in
the other 3 tables with just the information common to the other 3 tables
included.

I've tried using the phone number as the primary key. If I create a
relationship from the contact list to all the others will the contact list be
suddenly populated with the names from the other 3 lists. In other words, If
I open the contact list form, will the names from the other 3 appear in the
new database so I can use the phone dialer etc?

I guess the basic question is: Is it possible to have 3 separate lists merge
into 1 called the Contact list?

Thank you,

Sign me, One of the most frustrated people to have ever lived!

 

[Ed Warren]

Look under help for a UNION query.

Something like

Select FirstName as FName, LastName as LName, StreetAddress as Address1
From Addresslist1

Union

Select NameFirst as FName, NameLast as LName, AddressLine1 as Address1

From AddressList2

The key is each Select statement MUST have the same number, type, and named
fields.

OR you can Cheat and build a make table query on the first addresslist with
the 'common fields'
Then build an append query on each of the other address tables and append
them to the new table above (making sure you select the appropriate fields
to append to

Ed Warren.

 

[Guest]

As Ed points out, a UNION query will combine like sets of data from separate
tables. You could even combine his suggestions and use a query against your
UNION query as a make table query.

If this is a one-time event, and you'll never need to "combine" the three
separate tables again, follow Ed's suggestions.

If, however, you plan to continue using data from all three categories,
consider spending some time normalizing your data structure. If you have
three (essentially) identical table structures (name, address, phone),
differentiated only by the source of the data, you could stop using three and
use only one, by adding a field to designate source/category.

Regards,

Jeff Boyce
<Microsoft Office/Access MVP>