Relationship Question

Discussion in 'Microsoft Access Getting Started' started by NutritionHealer, Dec 10, 2005.

  1. 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!
     
    NutritionHealer, Dec 10, 2005
    #1
    1. Advertisements

  2. NutritionHealer

    Ed Warren Guest

    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.


    "NutritionHealer" <u16554@uwe> wrote in message news:589efdef146c3@uwe...
    > 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, Dec 10, 2005
    #2
    1. Advertisements

  3. NutritionHealer

    Guest 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>


    "NutritionHealer" wrote:

    > 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!
    >
     
    Guest, Dec 10, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Linda

    not able to update related records in a many to many relationship

    Linda, Jul 3, 2003, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    517
    John Vinson
    Jul 3, 2003
  2. kpec

    Table/Relationship basics' help

    kpec, Jul 23, 2003, in forum: Microsoft Access Getting Started
    Replies:
    0
    Views:
    357
  3. Guest

    Many to one relationship question

    Guest, Oct 14, 2004, in forum: Microsoft Access Getting Started
    Replies:
    5
    Views:
    211
    Guest
    Oct 14, 2004
  4. Randy

    Table Relationship Question?

    Randy, Aug 3, 2005, in forum: Microsoft Access Getting Started
    Replies:
    5
    Views:
    152
    Guest
    Aug 3, 2005
  5. Guest

    Relationship question

    Guest, Aug 20, 2007, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    163
    Arvin Meyer [MVP]
    Aug 21, 2007
Loading...

Share This Page