Thanks Ray,
Each customer has many orders
Each order has many orderlines
Each orderline has 1 product
Each product has 1 or more MetalCodes (SelectorNames)
Each SelectorName for that Product has a qty required (SelectorQty)
Each orderline may be scheduled over various dates with ShipETA &
ShipQty
--------------------------------
Basically this page is to total metalwork used for each product on
order.
i.e. product A may be made of the following METALWORK CODES:
19 x 1 piece
30 x 1 piece
Product B
115 x 1 piece
67 x 1 piece
98 x 1 piece
Each product is assigned to an orderline of an order.
i.e. there may be an order for a customer of:
Product B x 10 (ShipQty)
product B requires the following metalwork
115 x 1 piece (this is Selector Name * SelectorQty)
67 x 1 piece
98 x 1 piece
so,
We require a total of
115 x 10 pieces (this is SelectorQty x ShipQty). ShipQty is the amount
of that product which uses code 115 for that particular order.
67 x 10 pieces
98 x 10 pieces
for this single orderline, but, the 10 x Product B required are split
over various scheduled dates. (ShipQty)
We may require 5 (ShipQty) this Friday and 5 (ShipQty) next Friday, but
i'm just interested in the total sum of all (SelectorQty x ShipQty) for
each Metal code.
So there will always be the same metalwork codes in the system, just
linked to different orders and quantities.
I need to write out all the metalwork codes, and print next to each one
the sum of all the different orders that uses that code
i.e.
for the above order we need
code 115 x 10 pieces
for another 30 orders we may also need code 115 in various other
quantities, which need to be totalled up.
Your dictionary code listed all the metal codes correctly and some of
the figures were even correct, but only 6 out of about 15 tested, so
something is still wrong somewhere.
I am using the following SQL statement:
-----------------------
strQuery = "SELECT metalselector.SelectorName, SUM(SelectorQty*ShipQty)
AS TotalMW, metalselector.SelectorQty, Customers.CustomerName,
OrderLines.JobNumber, Orders.PONumber, OrderLines.OrderQuantity,
Sum(StockMovements.QtyShipped) AS SumOfQtyShipped,
OrderQuantity-Sum(QtyShipped) AS RemainingUnits, OrderLines.OrderNotes,
Products.ProductRangeID, OrderLines.SelectAll, PCBForecast.ShipQty,
Products.ProdCode, PCBForecast.HeldMarker, Products.ProductID,
PCBForecast.ShipETA"
strQuery = strQuery & " FROM ((Customers INNER JOIN (Orders INNER JOIN
(Products INNER JOIN (OrderLines INNER JOIN StockMovements ON
OrderLines.JobNumber = StockMovements.JobNumber) ON (Products.ProductID
= OrderLines.ProductID) AND (Products.ProductID =
OrderLines.ProductID)) ON Orders.OrderID = OrderLines.OrderID) ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN PCBForecast ON
OrderLines.JobNumber = PCBForecast.JobNumber) INNER JOIN metalselector
ON Products.ProductID = metalselector.ProductID"
strQuery = strQuery & " GROUP BY metalselector.SelectorName,
Customers.CustomerName, OrderLines.JobNumber, Orders.PONumber,
OrderLines.OrderQuantity, OrderLines.OrderNotes,
Products.ProductRangeID, OrderLines.SelectAll, PCBForecast.ShipQty,
Products.ProdCode, PCBForecast.HeldMarker, Products.ProductID,
PCBForecast.ShipETA, metalselector.SelectorQty"
strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
(OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"
strQuery = strQuery & " ORDER BY SelectorName;"
Set RS = adoDataConn.Execute(strQuery)
-------------------------------
Your code gives me a resulting list as, in metalwork (SelectorName
order)
But most of the figures are incorrect.
1 = 2220
2 = 2668
3 = 18
4 = 9
5 = 27
6 = 300
7 = 420
8 = 933
9 = 264
10 = 18
11 = 460
12 = 31
19 = 3
20 = 3
Thanks
David
-------------------------------------------------------------