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
-------------------------------------------------------------
Ray Costanzo [MVP] wrote:
Can you explain the database design and show the code that you're using now
to get the data?
Ray at work
"David" <da*********@scene-double.co.ukwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
Ray,
Thanks for the reply,
The page worked with your code, but i'm not exactly sure how or what it
produced:
It displayed the first field in my SQL (customername) and then = a
number, for each customer.
I need it to list , SelectorName (A) = sum of qty for SelectorName A
etc
Please can you explain further ?
Thanks
-------------------------------------------------------------------------------------------------------
Ray Costanzo [MVP] wrote:
I'd think that you could do this at the database level, but it's hard to
say
without seeing the database. So, for a code solution, this may be one of
the rare instances that a dictionary object comes into use.
Dim oDict
Set oDict = CreateObject("Scripting.Dictionary")
Do While Not yourRecordset.EOF
sKey = yourRecordset.Fields.Item(0).Value
sValue = yourRecordset.Fields.Item(1).Value
If oDict.Exists(sKey) Then
oDict(sKey) = CLng(oDict(sKey)) + CLng(sValue)
Else
oDict(sKey) = sValue
End If
yourRecordset.MoveNext
Loop
For Each oKey in oDict.Keys
Response.Write oKey & " = " & oDict(oKey) & "<br />"
Next
Set oDict = Nothing
Ray at work
"David" <da*********@scene-double.co.ukwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Hi,
I have an asp page which prints data as follows:
The sum in brackets is a calculated result on the page, SQ *
RS2("SelectorQty"), a variable SQ which holds a qty multiplied by a
record value
96 (50)
104 (50)
96 (100)
104 (100)
96 (100)
104 (100)
96 (100)
What I want to be able to do is print the page like this:
96 = 350 (where 350 is the sum of all items displayed as 96)
104 = 350 (where 350 is the sum of all items displayed as 104)
I would be very grateful for any help on this.
I cannot work out how to group or sum in the query as I have the first
SQL statement pulling data, then a second within the loop pulling the
number 96 & 104, then a calculation with done between data from both
SQL statements.
Thanks
