Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    15

    Unanswered: Complex query need help??

    Dear all

    i need to make the following query


    -mantable
    john
    david

    -DataTable
    ID man1name man2name
    1 john David
    2 john john

    need to make a query to be output

    ID manname one/two
    1 john 1
    1 david 2
    2 john 1
    2 john 2

    i have made already a query and it works fine but when man1name & man2name is the same name i got one row result
    ID manname one/two
    2 john 2

    and missing 2 john 1


    so any tips how can i make such query ??


    thanks all

    bye

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    SELECT ID, man1name, 1 as OneTwo
    FROM TableName
    UNION ALL
    SELECT ID man2name, 2 as OneTwo
    FROM TableName
    Paul

  3. #3
    Join Date
    Sep 2007
    Posts
    15

    done :)

    Quote Originally Posted by pbaldy
    Try

    SELECT ID, man1name, 1 as OneTwo
    FROM TableName
    UNION ALL
    SELECT ID man2name, 2 as OneTwo
    FROM TableName
    done

    thx alot

    but i have to do it in SQL view , can it done also using query deign view ???

    thx again

    bye

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can build the individual SELECT clauses in design view, but a UNION query can only be viewed in SQL view, so you'd have to put them together in SQL view.
    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
  •