none
How do I merging multiple queries into one query

    Question

  • I have an Excel workbook that is linked (connected) to an access table.  My problem is that the access table is the results of running 3 queries.  A make query, an append query and an update query.

    Is there a way I can have Excel execute the access procedure to run the three queries before reading?

    Or better yet, is these an SQL approach (I'm a newbie on SQL) where I can make a single query out of the three that can be used by Excel?

    My three example queries are below:

    MAKE QUERY:
    SELECT tblInspectionResults.EnteredDate, tblInspectionResults.LocalIndex, tblInspectionResults.Customer INTO tblTest
    FROM tblInspectionResults;

    APPEND QUERY:
    INSERT INTO tblTest ( EnteredDate, LocalIndex ) SELECT tblInspectionParts.EnteredDate, tblInspectionParts.LocalIndex FROM tblTest INNER JOIN tblInspectionParts ON tblTest.LocalIndex = tblInspectionParts.LocalIndex;

    UPDATE QUERY:
    UPDATE tblTest INNER JOIN tblInspectionResults ON tblTest.LocalIndex = tblInspectionResults.LocalIndex SET tblTest.Customer = [tblInspectionResults]![Customer] WHERE (((tblTest.Customer) Is Null));


    • Edited by PSD1953 Wednesday, January 9, 2019 2:42 PM
    Wednesday, January 9, 2019 2:41 PM

All replies

  • Hi,

    The short answer is a "no," but it depends. There is a way to execute one query to update existing records and also add new ones at the same time. However, it would mean you already have an existing table to do it. So, are you creating a temporary table to process the data? Meaning, each time you run this process, you discard the previous content of the temporary table?

    Wednesday, January 9, 2019 4:08 PM
  • Good question.  I am concatenating two tables, via a temporary table, and then making an update.  My concern is that I don't want to modify the two tables being concatenated.  How do I join two tables and make updates via a query so I don't have to save a temporary table?

    My last option is to do this concatenating and updating within Excel.

     
    Thursday, January 10, 2019 2:59 PM
  • The result of a query IS a “temporary” table, so you can use the name of the query in place of the “temporary” table. This will not affect either of the concatenated tables.

    I often run queries from VBA, from within Access. I imagine it’s possible to do that from within Excel, but I’ve not tried it.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, January 11, 2019 5:24 PM
  • Thanks Peter, that's what I was hoping for.  My problem now is how do I convert my three queries into one?  I guess I need SQL help.  Any suggestions?

    Thanks,
    -Pat

    Saturday, January 12, 2019 1:15 AM
  • I’m still a SQL newbie too! I rely on the Access query design tool to write the SQL for me.

    In Access, write the first query and save it.

    Write the second query such that when you need to refer to the results of the first query, simply use the name of that query. Save it as well.

    Finally, write the third query using the saved queries names. Run IT, and the final results are yours.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, January 12, 2019 3:45 AM
  • I've made some progress. I've been able to concatenate the MAKE & APPEND queries into one SQL query without creating a temporary.  Then my UPDATE query can to it's update to that query.  This gets me what I need - realtime access to the data.  Here are now my two 'select' queries.

    MAKE/APPEND (Test_01):
    SELECT tblInspectionResults.EnteredDate, tblInspectionResults.LocalIndex, tblInspectionResults.Customer FROM tblInspectionResults UNION SELECT tblInspectionParts.EnteredDate, tblInspectionParts.LocalIndex, "" AS Customer FROM tblInspectionParts ORDER BY LocalIndex;

    UPDATE (Test_02):
    SELECT Test_01.EnteredDate, Test_01.LocalIndex, tblInspectionResults.Customer
    FROM Test_01 INNER JOIN tblInspectionResults ON Test_01.LocalIndex = tblInspectionResults.LocalIndex;

    Executing Query 'Test_02' displays the expected results.

    Is there a way to concatentate these two queries? 

    Thanks,

    Tuesday, January 15, 2019 6:21 PM
  • PSD1953 –

    ??? If I understand correctly, the results are what you want. Is it not true that executing Test_02 is already concatenating the queries? Test_01 already appears in the SQL code for Test_02.

    I would put some code in a little VBA procedure of the order

        CurrentDb.Execute Test_02

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, January 15, 2019 9:07 PM