MS Access – How to Join Two Similar Queries Representing Different Years

join;ms accessselect

Rather new to Access (using 2016) and very sketchy on my SQL skill set.

First query totals sales by ALL THREE (user-supplied) criteria:

  1. Salesman
  2. Customer
  3. Product_Code
  4. [Year]

Second query does same except for [Year-1] where [Year] is a user supplied variable. This shows me current and prior total sales grouped by the three criteria shown above.

First Query will produce:

AKER   Cust1   Prod1    CurSales
AKER   Cust1   Prod2    CurSales
CLUT   Cust1   Prod3    CurSales
DELO   Cust3   Prod1    CurSales

etc…. in any combination of the three fields – all producing a total sales number for that combination for the CURRENT YEAR.

Second Query produces same, except for the PRIOR YEAR and may contain more (or fewer) combinations of the three fields.

I want third query to combine the two and show each combination that has a value of "Total Sales" in EITHER YEAR. I can then do comparisons using calculated fields.

I'm using ACCESS 2016 – primarily using the GUI.

For reporting, I need to do full outer join (I think) of these queries (i.e. show all records in both, joining rows where appropriate). I will then use this combined query to report and show comparative date.

I need help creating this second query. Do I need to create a unique "key" perhaps? Chasing my tail…..

Also, a crosstab can give me the exact results I want but I cannot then create a report because the field names are dynamic and I can't see myself writing VBA code at this juncture. I anyone knows how to accomplish this without VBA, I'm all good .

Best Answer

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 <User-specified criteria>
   AND Year = <user-specified year>

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 <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       FULL OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 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 <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       LEFT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 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 <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       RIGHT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 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!

Related Question