Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2013
    Posts
    7

    Unanswered: help with adding unique column to union query

    hello all,

    Let me first start this with saying that I'm very new to Access and SQL (Intern new) so please bare with me.

    The db which I was given was compiled of about 40 tables all from different periods which they were completed. I found that in order to simply the process of analyses I would build one master table and run comparison queries off of that. I eventually decided to do a union query in sql to put the tables together, first month-by-month, then year-by-year and finally they are all housed in one master table ready for analysis.

    I am now finding it may be useful to know the source of each entry in the master table (i.e. what table did it originally come from). Is there any sql syntax that I can run with the union query that will add a column to each entry with the title of the table it came from?

    Or would I have to go back into each table and manually add the column with the table name and then rerun the union queries? If so, is there a way I could build a macro to do this?

    Thanks for any help, again I'm very new so any tip/tricks are greatly appreciated!

    [edit]

    here's some sample code of the union:

    Select * from 02282012_1
    Union
    Select*from 03032011_1
    Union
    Select*from 03062012_1
    etc..



    so what I want to do is for example have every entry from 02282012_1 in the master table have a column with the original table name in it
    Last edited by mmongo; 02-25-13 at 14:39.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Select *, "02282012_1" As Source from 02282012_1
    Paul

  3. #3
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by pbaldy View Post
    Select *, "02282012_1" As Source from 02282012_1
    Thanks very much.

    Now how do I integrate this with my existing union query?

    Greatly appreciate it!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not sure I understand. It would be exactly like that in each SELECT statement, adjusted for each table.
    Paul

  5. #5
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by pbaldy View Post
    I'm not sure I understand. It would be exactly like that in each SELECT statement, adjusted for each table.
    Oh I see. So I just go ahead and replace the SELECT lines in the union query with what you gave me then?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure, adjusting for the corresponding table in the FROM clause.
    Paul

  7. #7
    Join Date
    Feb 2013
    Posts
    7
    Great, thanks you very much. I'll give it a go tomorrow and let you know the results

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem; post back if you get stuck. Welcome to the site by the way.
    Paul

  9. #9
    Join Date
    Feb 2013
    Posts
    7
    Everything worked great, thanks again for your help

Tags for this Thread

Posting Permissions

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