Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    6

    Unhappy Unanswered: Combining multiple tables into one

    Hi Guys,

    I have x3 tables.

    Let's call the as follows:

    comapps
    conapps
    proapps

    Each table contains certain similar fields such as fld_fname, fld_lname, fld_apptype.

    What I want to do is create a virtual table that combines all the data from the 3 tables and displays all the information in a set column structure.

    The structure of this virtual table would be something like the following:

    f_name l_name app_type
    ----------------------------------------------
    name1 lname1 con
    name2 lname2 pro
    name3 lname3 com

    As you can see, each record above originates from either the pro,con or com table. But the data is set to a fixed structure.

    I have been able to use left joins in the past, but the data is never structured in the correct format, it just adds each col to the end of the table

    Please help

    Best,

    JMC

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can use "UNION" if you want. Normally this is an argument to go for a supertype/subtype arrangement where all the similar fields for all types are stored in the same table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Yes union then but make sure the fields are in the same place as it join the table by stacking the data on top on each other
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Mar 2011
    Posts
    6

    Solved

    Hi Guys,

    Many thanks for this

    All solved

    Best,

    JMC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •