Summing values from different tables, grouped by values from different tables.

[eliash]

If I have four tables:
Products
Purchases
Sales
Destruction



The three latter tables refer to products from the first table (often the same product many times), using the same field name "product", and each have a column which tells how much of the product is treated. These columns have a different name for each table, respectively: "delivered", "ordered", "destroyed"

What I want to do is to generate the stock levels from these tables. The idea is to take the number of purchased items, grouped by product, and then subtract the sold and destroyed items, each grouped by their own product fields. (I would also be interested in how to write a general function of the three values for each product.)

The end result is a table where each row uniquely represents a product and has a field which shows the current stock level. I believe this is very possible and a favorable solution to auto-editing a static table representing the inventory. Please correct me if I am wrong, because I am rather new to Access.

Thank you.

[pbaldy]

One solution would be to create 3 totals queries on the 3 transaction tables. Each would group on product and sum the quantity. Then create a 4th query that includes the product table with a LEFT JOIN to each of those 3 queries. That will get you the quantities from each table, whether there's been activity or not.

[eliash]

Yes, this sounds like what I am looking for. I have managed to create the three totals queries, and tried to write a LEFT JOIN, but the syntax (or Access's interpretation of it, possibly?) troubles me. When you say "LEFT JOIN", would that be an inner or an outer join?

I would greatly appreciate if you could provide the code for this example (assuming the existence of the three totals queries, with the same name for the totals column in each of them).

[pbaldy]

It would be an outer, but in Access you can simply say

FROM ProductTable LEFT JOIN FirstQuery ON ...

You can also edit the default joins in query design view by right-clicking on the join lines and choosing the appropriate selection. Drop all 4 into a new query in design view, create your joins by clicking and dragging, then edit the joins. To be honest, I get confused as to how Access parenthesizes the joins sometimes, so that's how I would do it myself.

[eliash]

EDIT: SOLVED

Thank you, but I am not sure I follow. This left outer join, will it somehow sum the values, as I requested, or will it simply list them or a subset of them?

[eliash]

EDIT: SOLVED

After failing with LEFT JOINs I have tried a new method. The following code achieves what I requested, using the easier to read UNION operation:

SELECT Product, Sum(Total) AS Stock
FROM (SELECT ALL Product, Received AS Total
FROM Purchases
UNION SELECT ALL Product, (-1)*Ordered AS Total
FROM Sales
UNION SELECT ALL Product, (-1)*Destroyed AS Total
FROM Destruction)
GROUP BY Product;

It does, however, not list the Products by name, but by ID number. In order to solve this, I tried the following:

SELECT p.ProductID, p.Productname AS Product, Stock
FROM Products AS p LEFT JOIN
(SELECT Product, Sum(Total) AS Stock
FROM (SELECT ALL Product AS ProductID, Received AS Total
FROM Purchases
UNION SELECT ALL Product AS ProductID, (-1)*Ordered AS Total
FROM Sales
UNION SELECT ALL Product AS ProductID, (-1)*Destroyed AS Total
FROM Destruction)
GROUP BY Product) AS q
ON p.ProductID=q.ProductID;

This returns the following error message:
"The inclusion of constant expressions in an outer join operation is not supported."

I have found nothing about this error message on the web. Can anyone see where the problem is, or point it out if this depends on more information about the particular tables?

[eliash]

I solved it. Thanks for helping me keep trying. ;-)

[pbaldy]

And all that while I slept!

Glad you got it sorted out.

[eliash]

I think I owe to this forum to post the solution:

SELECT p.Productname AS Product, u.Stock
FROM Products AS p LEFT JOIN
(SELECT l.ProductID AS ProductID, Sum(Total) AS Stock
FROM
(SELECT ALL Product AS ProductID, Received AS Total
FROM Purchases
UNION SELECT ALL Product AS ProductID, (-1)*Ordered AS Total
FROM Sales AS s WHERE s.Ordered>0
UNION SELECT ALL Product AS ProductID, (-1)*Destroyed AS Total
FROM Destruction) AS l
GROUP BY l.ProductID) AS q
ON p.ProductID=q.ProductID;

There may be typographical errors from my translation, but the principle should be right (or at least seems to work all right for me).