Algorithm Help

[digimortal]

Hi,
I got Listboxs like this:
First Second
1 3
1 4
1 3
2 5
3 4
3 2
4 1
5 7
5 5

What I need is to remove the duplicate items from the first one and sum all the corresponding values in the second one, so the example become:

1 10
2 5
3 6
4 1
5 12

This is the code I wrote but it does not work;

        While RowCounter < ColorListB.Items.Count - 2
            RCG = RowCounter+1
            While ColorListB.Items.Item(RowCounter).Text = ColorListB.Items.Item(RCG).Text
                QuantityListB.Items.Item(RowCounter).Text = CDbl(QuantityListB.Items.Item(RowCounter).Text) + CDbl(QuantityListB.Items.Item(RCG).Text)
                ColorListB.Items.RemoveAt(RCG)
                QuantityListB.Items.RemoveAt(RCG)
                RCG += 1
            End While
            RowCounter += 1
        End While

 

[SBendBuckeye]

Is this being populated by a query? If so, try something like this:

Select fld1, Sum(fld2) 
From table1
Group By fld1
Order By fld1

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[digimortal]

I Hope this will explain what I'm trying to do...
I know this is not SQL forum but...

SELECT SUM(I.QUANTITY), (SELECT DISTINCT COUNT(COLOR) FROM COLORTABLE WHERE I.MATERIAL = MATERIAL GROUP BY MATERIAL, REVISION) as COL
FROM SOLDITEMS I, COLOR C WHERE I.QUNIT = 'KG' AND I.CREATEDAT > '01.06.2005' AND I.CREATEDAT < '30.06.2005'
GROUP BY COL

COLORTABLE holds the colors used in a product in different records so I count them to get the number of colors used for an item.

This is what I'm trying to do:
We sold 100kg of items we use 2 color for
We sold 233kg of items we use 7 color for
etc...

I hope someone helps 'cause I'm in big trouble here...

 

[ca8msm]

What are the relationships between the tables as you appear to have a cartesian join in your query (i.e. you have "FROM SOLDITEMS I, COLOR C" but those tables don't have a join). It should be fairly straight-forward to do this in SQL if you could give an explaination of your table structure.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[digimortal]

The relation is the material fields.
First I get the Materials and Sold Quantities like:
Material Quantity
X 100
Z 500
K 1000

my color table:
COLOR MATERIAL
Blue X
Red X
Black Z
Blue K
Yellow K

So I need:
COL Material
2 X
1 Z
2 K

And last stage:
COL Quantity
1 500
2 1100 (1000+100, X+K Because they use 2 colors)

 

[ca8msm]

Just to explain a bit more of what I mean, I would have expected the resulting SQL to look something along the lines of:

SELECT I.QUANTITY, SUM(CT.COLOR)
FROM SOLDITEMS I, COLOR C, COLORTABLE CT 
WHERE I.COLORCODE = C.COLORCODE
AND C.COLORCODE = CT.COLORCODE 
AND I.QUNIT = 'KG' 
AND I.CREATEDAT > '01.06.2005' 
AND I.CREATEDAT < '30.06.2005'
GROUP BY I.QUANTITY

e.g. there are joins from the solditems table to the color table, then from the color table to the colortable table.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.