Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    15

    Unanswered: Subject: Append query

    Hi,
    I wonder if you could help me.
    I have created an Access DB & I have 3 queries in there with identical numbers of fields & field name/type. The only thing is, the data is different in each query.
    I would like to end up with one query which contains the data from all individual queries. The queries have the following data
    Query1:
    orderNo. Order value Description
    2006/01 1000 Desc1
    2006/02 1200 Desc2

    Query2
    orderNo. Order value Description
    2007/01 250 Desc3
    2007/02 1000 Desc1
    2007/03 3000 Desc2

    Query3
    orderNo. Order value Description
    2008/01 1250 Desc2
    2008/02 1000 Desc3
    2008/03 5000 Desc2

    I would like to end up with the following query
    orderNo. Order value Description
    2006/01 1000 Desc1
    2006/02 1200 Desc2
    2007/01 250 Desc3
    2007/02 1000 Desc1
    2007/03 3000 Desc2
    2008/01 1250 Desc2
    2008/02 1000 Desc3
    2008/03 5000 Desc2

    Thanks.

    Samy5

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this a one off or is this going to be a regular feature

    if its a one off I'd 'just' copy and paste the data

  3. #3
    Join Date
    Jan 2008
    Posts
    15
    hi. Thanks for responding. No it's not a 'one off'. The tables behind the individual queries will grow so at a given point I need to run each query & I'd like to combine the data from the queries into a single query which will feed into a report.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Create a UNION query.

    You get the each query's SQL and join them together with the word UNION.

    EG:
    SELECT OrderNo, OrderVal, Description FROM TableX WHERE <expression1>
    UNION
    SELECT OrderNo, OrderVal, Description FROM TableX WHERE <expression2>
    UNION
    SELECT OrderNo, OrderVal, Description FROM TableX WHERE <expression3>

    etc.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    Code:
    SELECT OrderNo, OrderVal, Description FROM TableX WHERE <expression1>
    UNION
    SELECT OrderNo, OrderVal, Description FROM TableX WHERE <expression2>
    UNION
    SELECT OrderNo, OrderVal, Description FROM TableX WHERE <expression3>
    Psst, if they're all using "TableX" then that's equivalent to
    Code:
    SELECT OrderNo
         , OrderVal
         , Description
    FROM   TableX
    WHERE  <expression1>
     OR    <expression2>
     OR    <expression3>
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2008
    Posts
    15

    blank text field

    Thanks very much for that. I tried the union query & it worked.
    I have one more question please.
    I am trying to get the syntax of a query expression right.
    The field ‘Description’ is a text field & I am trying to extract records the ‘Description’ is blank.
    I have used expressions such as:
    =””
    =” “
    LIKE “ “
    IsNull(Description)
    None of the above work. However if I use <>”” it brings back all records which contain some text in the ‘Description’ field.
    Any ideas?

    Samy5

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is Null OR =""
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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