Need to join two similar queries that represent different years
As you note, you need to do a full outer join between the two queries.

Let's assume your first query (for the user-specified year) is:

SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
  FROM SalesTable
 WHERE &lt;User-specified criteria&gt;
   AND Year = &lt;user-specified year&gt;


Then, the combined query should look like this:

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE &lt;User-specified criteria&gt;
           AND Year = &lt;user-specified year&gt;
       ) as cy
       FULL OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE &lt;User-specified criteria&gt;
           AND Year = &lt;user-specified year&gt; - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )


NOTE: COALESCE() may be ISNULL() in Access, or fall under some other name - it returns the first value of the values passed to it that isn't NULL (or NULL, if all values are NULL).

Also: as I'm not particularly familiar with Access itself, this is in SQL Server syntax. I think modern versions of Access should be OK with that, but I'm not sure.

How you would convince a GUI query builder to do this for you, I have no idea - most of them have difficulty with more complex queries. Hopefully, Access will let you create the query in a text editor and paste it in, if nothing else.



Well, since apparently MS Access doesn't have FULL OUTER JOIN, we need to get trick it into giving us we what want.

A FULL OUTER JOIN of A and B on A.col = B.col gives you three things:


The combination of all rows from A and B where there's a matching row in both tables;
All rows from A where there's no matching row in B (with the columns from both A and B, where all B columns are NULL); and
All rows from B where there's no matching row in A (with the columns from both A and B, where all A columns are NULL).


Now, A LEFT OUTER JOIN B ON A.col = B.col gives us the first two of the three items above.

So, we need to a way to get just item 3 above, and then combine that with the results of A LEFT OUTER JOIN B.

See if this (overly complicated) query seems to work:

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE &lt;User-specified criteria&gt;
           AND Year = &lt;user-specified year&gt;
       ) as cy
       LEFT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE &lt;User-specified criteria&gt;
           AND Year = &lt;user-specified year&gt; - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )

UNION ALL

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE &lt;User-specified criteria&gt;
           AND Year = &lt;user-specified year&gt;
       ) as cy
       RIGHT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE &lt;User-specified criteria&gt;
           AND Year = &lt;user-specified year&gt; - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )
 WHERE (    cy.Salesman IS NULL
        AND cy.Customer IS NULL
        AND cy.Product_Code IS NULL
        AND (   py.Salesman IS NOT NULL
             OR py.Customer IS NOT NULL
             OR py.Product_Code IS NOT NULL
            )
       )


UNION ALL combines the results of two queries (with compatible SELECT lists) into one result set.

The first query, A LEFT OUTER JOIN B, gives us items 1 and 2 from our bullet list above.

If we simply created a query, A RIGHT OUTER JOIN B, we'd get items 1 and 3 from the bullet list.

By adding the WHERE clause, where we only accept rows from the RIGHT JOIN where our join columns from A are NULL, and at least one of our join columns from B is not NULL. The rows from item 1 will all have matching values in A and in B - so, either they will have a non-NULL value in at least one of the join columns, or all the join columns from both A and B will be NULL. So, this should just give us the rows from item 3 in our bullet list.

NOTE: this is all untested - please confirm that it works before using in production code!