Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Multiple Input files to an Append Query

Discussion in 'Information Technology' started by Andreww, Jul 28, 2006.

  1. Andreww

    Andreww
    Expand Collapse
    Guest

    Hi - I have tried all ways of trying to do this.

    I can create a query with one input file - when I run the query it
    works fine.

    When, using design/query view I have tried adding additional input
    files - doesn't work "duplicate output destination 'output' "!!!

    Is it infact possible to do this?

    If so, do I just add in more tables... or what?

    Thanks

    Andrew
     
  2. Loading...


  3. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello Andrew.

    "Andreww" wrote:
    > I can create a query with one input file - when I run the query it
    > works fine.
    >
    > When, using design/query view I have tried adding additional input
    > files - doesn't work "duplicate output destination 'output' "!!!


    I think you want a UNION query.The easiest way to do so is:
    Create queries for all data you want to append, using corresponding
    fields in the same order in the select list (do not sort the records)
    Then, create a query, add no tables, click "SQL-Specific, Union" in
    the query menu.
    Now, paste all SQL Statements into the new query, deleting the ";"
    between them and separating them with a UNION ALL.
    Behind that large query string, you are allowed to use a ORDER BY
    clause, sue the fieldnames of the first select statement for that.
    Then you can create an append query based on this union-query.

    --
    Regards,
    Wolfgang
     
  4. Andreww

    Andreww
    Expand Collapse
    Guest

    Thanks Wolfgang - I'll give it a go.

    Andrew
     
  5. Andreww

    Andreww
    Expand Collapse
    Guest

    I don't quite understand this... assume "sue the.." is a typo?

    "Behind that large query string, you are allowed to use a ORDER BY
    clause, sue the fieldnames of the first select statement for that.
    Then you can create an append query based on this union-query. "

    Would you mind explaining?

    Also, do I need to specify each field or can I use a wildcard?

    Regards

    Andrew
     
  6. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello Andrew.

    "Andreww" wrote:
    > I don't quite understand this... assume "sue the.." is a typo?


    Yes, sorry, I ment "use". This concerns only the sorting of records.

    > "Behind that large query string, you are allowed to use a ORDER BY
    > clause, sue the fieldnames of the first select statement for that.
    > Then you can create an append query based on this union-query. "
    >
    > Would you mind explaining?


    A Union query can consist of many queries that must not have an "order by"
    clause.
    The whole union query may have an "order by" clause in the end, if you want
    to sort the records (before appending them in your case).

    > Also, do I need to specify each field or can I use a wildcard?


    In the union query: YES, provided that the field order is consistent.

    --
    Regards,
    Wolfgang
     

Share This Page