Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9

    Unanswered: Best way to.... merge tables or SQL query

    OK ... Access noob learning by trial and error.

    I have an access db with 5 tables. Each table column heading is identical. Each row contains different data.

    I want to set up a query to search all of the tables for one or more results based on different user requests (last name, phone number, etc) and report back the all columns of data in the row queried.

    Whats the best way to set up the query?

    Can the tables be merged and set up with a basic query?

    For me...Single table no problem... this format, big problem.
    Steve

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT * FROM (
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table1
    ) AS mydata
    WHERE somecolumn = 'summat'
    AND oddercolumn = 'something'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9
    Amazing ...

    Only one issue ... the criteria and ordering are grayed out when I try to set up the search options ..
    Steve

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    grayed out???

    oh, and i hope you realize what i meant, even though i have table1 in there 5 times (copy/paste)

    another thing you can do is that the union query and save it, then your main query can be --

    SELECT * FROM unionquery
    WHERE somecolumn = 'summat'
    AND oddercolumn = 'something'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9
    Getting there ....

    Im gonna play with it and see where I end up.

    Thanks for your time!
    Steve

  6. #6
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9
    DONE!

    THANKS A TON!!!!

    I played with the code and got what I wanted. Perfect.

    Check out your help ... www.npi-search.com
    Steve

Posting Permissions

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