
organizing repeating data in array
I have a dbase table with order details in it and I would like to take this information and compact it so that repeating rows are put into one row with the total quantity reflected. For example, if 2 rows in the table are:<BR>1 / red / shirt / large<BR>3 / red / shirt / large<BR><BR>Then I want to be able to display it in one row of an array as:<BR>4 / red / shirt / large<BR><BR>I have taken and put the table info into an array and tried to loop through it and sort the info into a new array, however I'm currently getting "Type mismatch: 'ubound'" as an error. Any guidance is much appreciated. Below is my code:<BR><BR><%dim prod_RS<BR>Set prod_RS = server.CreateObject("ADODB.Recordset")<BR>prod_RS. ActiveConnection = MJ_connec<BR>prod_RS.Source = "Select fabtype, fabcolor, fabquantity, fabtotal, wholesale From orderdetails"<BR>prod_RS.CursorType = 0<BR>prod_RS.CursorLocation = 2<BR>prod_RS.LockType = 1<BR>prod_RS.Open()<BR>%><BR><BR><%<BR>& #039;put prod_RS into an array<BR>Dim array_info<BR>const prod_type = 0<BR>const prod_color = 1<BR>const prod_quant = 2<BR>const prod_gross = 3<BR>const prod_net = 4<BR><BR>array_1 = prod_RS.GetRows()<BR>prod_RS.Close<BR>%><BR>& #060;%<BR>for row=0 to ubound(array_1, 2)<BR>num_rows = row+1<BR>next<BR>%><BR><%<BR>'creat e new array to put values into<BR>dim new_array<BR>redim new_array(5, num_rows)<BR>const prod_type2 = 0<BR>const prod_color2 = 1<BR>const prod_quant2 = 2<BR>const prod_gross2 = 3<BR>const prod_net2 = 4<BR>'insert new values, update quantity for repeating values<BR>for i=0 to ubound(array_1)<BR> for y=0 to ubound(array_2)<BR> if array_1(prodtype, i) = array_2(prodtype2, y) then <BR> if array_1(prodcolor, i) = array_2(prodcolor2, y) then<BR> array_2(prodquantity, y) = array_2(prodquantity, y) + array_1(prodquantity, i)<BR> end if<BR> end if<BR> if array_1(prodtype, i) <> array_2(prodtype2, y) then<BR> if array_1(prodtype, y) <> array_2(prodtype2, y) then<BR> array_2(prod_type2, y) = array_1(prod_type, i)<BR> array_2(prod_color2, y) = array_1(prod_color, i)<BR> array_2(prod_quant2, y) = array_1(prod_quant, i)<BR> array_2(prod_gross2, y) = array_1(prod_gross, i)<BR> array_2(prod_net2, y) = array_1(prod_net, i)<BR> end if<BR> end if <BR> next<BR>next <BR>%>

Trivial, but why the array?
Select fabtype, fabcolor, SUM(fabquantity) AS totalQuantity<BR>From orderdetails<BR>GROUP BY fabtype, fabcolor<BR><BR>See my response to Felicity in the DB forum about understanding GROUP BY.<BR><BR>Doing it via arrays is really really clumsy. Not to mention slow.<BR><BR><BR>

RE: Trivial, but why the array?
Thanks Bill, I didn't think of doing it that way.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

