Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2014
    Posts
    11

    Unanswered: Combining Columns from Multiple Tables into a New Table

    Hi, I have a bunch of tables on my database (well who doesn't)? In most of the tables I have a field called CallName, which is just a short title. They are all short text fields (identical). What I want to do is combine all those CallName fields into one feild in a different table so that I can use it for a combo box.

    Is this an Append Query or a Make Table Query? Or do I need to create an empty table, then put them into it somehow?

    This seems like I should know how to do this, but can't get it straight. Thanks for any help!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If I understand correctly, a union query:

    SELECT CallName
    FROM Table1
    UNION ALL
    SELECT CallName
    FROM Table2
    Paul

  3. #3
    Join Date
    Oct 2014
    Posts
    11
    I thought I posted this already, but don't see it, sorry if it's twice.

    I did exaclty what you are saying to do, but keep getting that error message about invalid slq: execpted Delete, Insert, etc.

    Here is my sql language. Can you figure this out? Thanks!

    SELECT CallName
    FROM tblDCharactersOW
    UNION ALL
    SELECT CallName
    FROM tblDProjects
    UNION ALL
    SELECT CallName
    FROM tblMMEpisodesOrChapters
    UNION ALL
    SELECT CallName
    FROM tblMMScenes
    UNION ALL
    SELECT CallName
    FROM tblDManualTags
    UNION ALL
    SELECT CallName
    FROM tblMMEvents
    UNION ALL
    SELECT CallName
    FROM tblSBooks
    UNION ALL
    SELECT CallName
    FROM tblFBookExcerpts
    UNION ALL;

    I also tried
    Select Callname
    From (and listed all the tables with commas between them)

    That didn't work either.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The "UNION ALL" joins clauses, so shouldn't be at the end
    Paul

  5. #5
    Join Date
    Oct 2014
    Posts
    11
    Hi, now I'm having another problem. I got this query to work, but what I want is to create a permanent table that will update from each table as new "CallNames" are added. My purpose for this table is to use the results as a combobox so that I can choose any one of them. My understanding is that it has to come from a table (or query based on a table) because it has to have a unique identifier. Thanks for any help.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    why just do a MAKE table base on your UNION query
    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.

  7. #7
    Join Date
    Oct 2014
    Posts
    11
    Hi, thanks for the reply. Maybe I don't understand this.

    Can I:

    1. Make a table and add an autonumber to it?

    2. Run the querty again with results so that ONLY THE NEW CallNames will be appended into that made table, but the already existing entries won't change? Because if it runs the query again and mixes everything up, my combo box will be messed up. How would I do that?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sounds to me that your time would be better off spent redesignibg yhe dara model. Consider using the sub/super type approach.
    have a table gor all calls, then use 'child' tables to store information pertinent to different table types
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I agree with healdem, but if you stick with your design, I wouldn't try to maintain a table with the joined values, just use the union query as the combo source. I'm sure the key issue can be worked around.
    Paul

Posting Permissions

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