Grouping & Sum of result page

D

David

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
 
R

Ray Costanzo [MVP]

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
 
D

David

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
 
R

Ray Costanzo [MVP]

Can you explain the database design and show the code that you're using now
to get the data?

Ray at work
 
D

David

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





-------------------------------------------------------------
 
D

David

No worries, i've fixed it hopefully, the problem was to do with using
sum in the SQL.
Your code works a treat ..... even though I have never used this
object, .... cool... nice one !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top