Results 1 to 2 of 2

Thread: combine two Access Jet SQL queries into one

  1. #1
    Join Date
    Jan 2020
    Posts
    1

    combine two Access Jet SQL queries into one

    These are two queries that I use sequentially in order to produce a limited-scope crosstab output from what would otherwise by many more columns:

    TRANSFORM PackageID
    SELECT Process
    FROM Sheet1
    WHERE Process IN ('Recovery','Extraction','Forming')
    GROUP BY PackageID
    PIVOT Process;

    I name the above statement Query1. The problem with this query is that 4 columns are returned instead of only the 3 that I want displayed. (The fourth column appears at the far left and is headed "Process" but predictably its contents underneath are irrelevant.)
    Then to limit the output to only 3 columns, I have a second query, which calls the above:

    SELECT Query1.Recovery, Query1.Extraction, Query1.Forming
    FROM Query1;

    How can the two statements above be combined into one?
    Last edited by s430; 01-26-2020 at 05:01 PM.

  2. #2
    Join Date
    Nov 2020
    Posts
    35
    Can nest the first within the second: FROM (first query sql here) AS Query1;

    Instead of WHERE clause, specify fields in PIVOT clause: PIVOT Process IN('Recovery','Extraction','Forming');

Tags for this Thread

Posting Permissions

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