Access VBA How to use a Union ALL statement

Question

My UNION ALL statement is not returning what I hoped it would. I am putting products into a location (73) and taking them out of the same location. I would like to know how many are remaining in that location. I am trying to figure this out by adding the amount in and subtracting the amount out. I am storing my transactions in tblWarehouseTransfer.
I would like to have one line for each product with the total. What I am getting is one line with the sum of the amount put into the location and one line with the sum of the amount taken out (as a negative number).
I am using a list box to display the list of all my products.

Me.lstCutWipers.RowSource = "SELECT tblProducts.ProductID, tblProducts.ProductName, Sum(tblWarehouseTransfer.Qty) AS SumOfQty " _
            & " FROM tblWarehouseTransfer INNER JOIN tblProducts ON tblWarehouseTransfer.ProductID = tblProducts.ProductID " _
            & " GROUP BY tblProducts.Productid, tblProducts.ProductName, tblWarehouseTransfer.LocationTo " _
            & " HAVING (((tblWarehouseTransfer.LocationTo) = 73)) " _
            & " UNION ALL SELECT tblProducts.ProductID, tblProducts.ProductName, -Sum(tblWarehouseTransfer.Qty) AS SumOfQty " _
            & " FROM tblWarehouseTransfer INNER JOIN tblProducts ON tblWarehouseTransfer.ProductID = tblProducts.ProductID " _
            & " GROUP BY tblProducts.Productid, tblProducts.ProductName, tblWarehouseTransfer.LocationFrom " _
            & " HAVING (((tblWarehouseTransfer.LocationFrom)= 73))"

Can someone help me to join the 'in' and the 'out' as one total.

A UNION query will not sum the columns of two queries together, so you will not get one line with a grand total. The purpose of UNION is to combine (not as in sum, rather as in list together) the rows of one query with another (and optionally filter out duplicate rows). — C Perkins, May 21 '17 at 03:11
BTW, Welcome! Since you are new to posting on Stack Overflow (SO), I will go ahead and post an answer to help you get started. But please at least edit your question title to something more like "Add two sums from two different queries", because ultimately that is what the question is about and UNION was simply a failed attempt. SO can be a wonderful resource and community, but you'll find that most people here are very particular about formulating your question correctly and/or updating it to be more accurate and provide better details. — C Perkins, May 21 '17 at 03:23

C Perkins · Accepted Answer · 2017-05-21T05:10:55.770

This example joins two subqueries which allows your two different sums to be added together, whereas a UNION only lists rows of the two queries together.

One downside to having subqueries is that it cannot be fully edited in query Design View... it requires the SQL View to edit the whole thing. BUT, you could save each subquery separately and then join those queries together in a third query. Then you could edit each part separately in Design View.

Also notice that I changed the HAVING clause to a WHERE clause. WHERE clauses can be more efficient if you are applying criteria to source values before they are aggregated (i.e. grouped and summed). HAVING applies the criteria after aggregating the data. If the criteria involves aggregate expressions, then they must appear in HAVING clause.

By changing to a WHERE clause it also means that you don't have to group on that field. The difference in speed may be negligible and it should return the same information, but just not necessary since every row contributing to that query will only be for the value in the WHERE clause. Just be aware that if you change the query at all, you need to consider the proper clause to apply criteria.

EDIT: Changed to LEFT JOIN and handled NULL in TotalSum with call to nz().

SELECT ToQuery.ProductID, ToQuery.ProductName, (ToQuery.SumOfQty + nz(FromQuery.SumOfQty, 0.0)) As TotalSum
FROM
  (SELECT tblProducts.ProductID, tblProducts.ProductName, Sum(tblWarehouseTransfer.Qty) AS SumOfQty
  FROM tblWarehouseTransfer INNER JOIN tblProducts ON tblWarehouseTransfer.ProductID = tblProducts.ProductID
  WHERE tblWarehouseTransfer.LocationTo = 73
  GROUP BY tblProducts.Productid, tblProducts.ProductName) AS ToQuery
LEFT JOIN
  (SELECT tblProducts.ProductID, tblProducts.ProductName, -Sum(tblWarehouseTransfer.Qty) AS SumOfQty
  FROM tblWarehouseTransfer INNER JOIN tblProducts ON tblWarehouseTransfer.ProductID = tblProducts.ProductID
  WHERE tblWarehouseTransfer.LocationFrom = 73
  GROUP BY tblProducts.Productid, tblProducts.ProductName) AS FromQuery
ON ToQuery.ProductID = FromQuery.ProductID

To be complete, this assumes that ProductID is a primary key and that ProductName is unique to each ProductID. If that is not true, you will need to change the outer query ON expression to match ProductName values as well (i.e. add AND ToQuery.ProductName = FromQuery.ProductName).

@C Perkins: thank you very much for the answer and the information about Union. Unfortunately, the code does not give me the exact answer I require. The code successfully totals and sums on one line all the products that have entries in tblWarehouseTransfer for locationTo and locationFrom i.e. in and out. It doesn't show the entries that are only 'in' i.e. haven't been taken 'out' yet. (Yes ProductID is the primary key and ProductName is unique) — Les, May 21 '17 at 04:23
@Les I trusted that your queries where otherwise correct--nobody can guess what criteria you need applied. You also stated "I would like to have one line for each product with the total." Now that I've demonstrated how to combine and sum values from multiple queries, it's up to you to change the criteria and make sure it includes all rows that you require. For debugging what rows you need, I suggest running each subquery separately, and even removing the GROUP BY to inspect each row. Don't forget to read my other comments and edit your question to be more accurate and useful to other users. — C Perkins, May 21 '17 at 04:50
Okay, I think it finally clicked what you meant after I already posted my last comment, but it doesn't really change what I said. I want to help, but really it's a whole new question, since I must assume that to some extent you already understand about joins, etc. It is difficult to teach all details about SQL and queries all in one question.... You need to change the join on the subqueries. It is only returning rows that have matching "in" and "out" (assuming those translate to having values in LocationTo and LocationFrom). Changing the join will result in NULL values. — C Perkins, May 21 '17 at 05:05
@C Perkins Thanks again. I really don't know joins that well. I would never have gotten that answer using 'left join'. Obviously, there's a lot more for me to learn (which is why I originally tried UNION for the 2 subqueries). Thank you for the guidance. I will try to be better in any future questions. — Les, May 21 '17 at 05:37
For anyone else needing to learn about joins, I now found this excellent reference in stackoverflow: [learn about 'join'](http://stackoverflow.com/questions/19267238/what-is-difference-between-inner-join-and-outer-join) — Les, May 21 '17 at 05:59
@Les I'm glad it all helped and I hope that you continue to learn and enjoy it. I have seen others be rather quick to close questions and give very sharp comments, even for new people on the website. I am generally more patient, so I like to help but also encourage newbies on how to formulate good questions and be aware of the culture here. — C Perkins, May 21 '17 at 17:52

Access VBA How to use a Union ALL statement

1 Answers1