Well, you need to figure out what it is you want to see - then, you need to
link the appropriate tables together in the right way.
In this case:
For every OrderNo-Bproduct pair i want to sum the conQTY.
conQTY and OrderNo are both in the Containers table. Bproduct is in both the
Invoices and OrderDetails tables - you can join Containers to Invoices using
the SInvoiceNo field, or you could join Containers to OrderDetails using the
OrderNo field. Which way you go depends on how well the dtabase keeps these
things in synch.
You create a join in Query Designer by dragging the field from one table and
dropping it onto the corresponding field in the other table.
Then, you choose the fields you want - in this case, OrderNo, BProduct and
conQTY. If you look at the output of this, you'll see that it isn't added up,
but you get all the individual records. To add them together, you need to
right click on the bottom part of the Query Designer - the section where all
the fields are - and choose "Totals". In the "Total:" field that appears, you
want to "Group By" OrderNo and BProduct, and you should change conQTY from
"Group By" to "Sum". Now when you look at the output, you should get the
conQTY totalled for each OrderNo and Bproduct.
If you put the query together right, your SQL should look like this:
SELECT OrderDetails.OrderNo, OrderDetails.Bproduct, Sum(Containers.conQTY) AS
SumOfconQTY
FROM Containers INNER JOIN OrderDetails ON Containers.OrderNo = OrderDetails.
OrderNo
GROUP BY OrderDetails.OrderNo, OrderDetails.Bproduct;
I've used OrderDetails here because you ultimately want to compare it against
the ordered quantity. This makes it easy to do so, simply by adding ordQTY
and making sure it's also Summed instead of Grouped. The SQL would look like
this:
SELECT OrderDetails.OrderNo, OrderDetails.Bproduct, Sum(Containers.conQTY) AS
SumOfconQTY, Sum(OrderDetails.ordQTy) AS SumOfordQTy
FROM Containers INNER JOIN OrderDetails ON Containers.OrderNo = OrderDetails.
OrderNo
GROUP BY OrderDetails.OrderNo, OrderDetails.Bproduct;
Note that this wouldn't show any orders for which no containers have been
received - that's because we're joining the two tables together such that
only records that appear in both will appear ie. those orders for which
containers have been received. To sum all orders, even if no container has
been received, we need to alter the Join Properties in Query Designer from "1.
Only include rows where the joined fields from both tables are equal" to
"Incllude ALL records from 'OrderDetails' and only those records from
'Containers' where the joined fields are equal". This is called an "outer
join", and the resulting SQL looks like this:
SELECT OrderDetails.OrderNo, OrderDetails.Bproduct, Sum(Containers.conQTY) AS
SumOfconQTY, Sum(OrderDetails.ordQTy) AS SumOfordQTy
FROM Containers RIGHT JOIN OrderDetails ON Containers.OrderNo = OrderDetails.
OrderNo
GROUP BY OrderDetails.OrderNo, OrderDetails.Bproduct;
If you can also receive Containers for which there is no corresponding order,
we then have a problem that we would need to do some otheer things for...
we'll cross that bridge if it becomes necessary.
Let us know if you've gotten lost somewhere along the way there, and I'll try
and explain it again. Hope that helps!
