calculate on dynamic data 2

[kiwieur]

I posted this question in the javascript forum

I have a web page that displays data in a table from a databse however the number of rows can change based on the criria selected.

I have a column that shows the order qty for a particular specification however there are 2 types of order

1. make product for stock
2. call off order from stock

If anyone could point me in the right direction it would be much appeciated

such as

spec_number order type order qty
12345 make stock 10000
12345 call off stock 5000

what i would like is to have another column that shows current stock like so

spec_number order type order qty current
12345 make stock 10000 10000
12345 call off stock 4000 6000
12345 make stock 5000 11000

is it possible to recreate this so that it looks at the current stock on the line above and depending on the stock type either adds or deletes the order qty to the relevant row field.

**********************************************************

and it was suggested by Dan that

"This is very possible, however, given you've got server-side scripting to read from a database, I'd suggest doing this server-side rather than client-side."

So I was wondering whether anyone could point me in the right direction please

This is my access SQL

SELECT tblTopUpCallOffOrders.spec_number, tblTopUpCallOffOrders.date_ordered, tblTopUpCallOffOrders.due_date, tblTopUpCallOffOrders.Job_number, tblTopUpCallOffOrders.QtyOrdered, tblTopUpCallOffOrders.OrderType, 0 AS CurrentStock
FROM tblTopUpCallOffOrders
WHERE (((tblTopUpCallOffOrders.spec_number)="229895"))
ORDER BY tblTopUpCallOffOrders.due_date;



Regards

Paul

 

[DotNetGnat]

my bad...try this...

...
<% 
Dim CStock
While ((Repeat1__numRows <> 0) AND (NOT rsRunSum.EOF)) 
%>
  <%If (Repeat1__numRows Mod 2) Then%>
<tr bgcolor="#FFFF99" class="TextCentrePlain10px">        
<%else%>
<tr bgcolor="#CCFFFF" class="TextCentrePlain10px">
<%End If%>
    <td><%=(rsRunSum.Fields.Item("DDate").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("SpecNo").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderNo").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderType").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Qty").Value)%></td>
   <%
If not rsRunSum.EOF Then
[red]If CStock&"" = "" Then[/red]
CStock = rsRunSum.Fields.Item("WHStock").Value
End If

OrderType = rsRunSum("OrderType").value
OrderQty = rsRunSum("Qty").value
select case (OrderType)
case "CallOff"
CStock = CStock-OrderQty
case "TopUp"
CStock = CStock+OrderQty
case else CStock = CStock
end select
%>
<td><%=(CStock)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsRunSum.MoveNext()
end if
Wend
%>
...
...

-DNG

 

[kiwieur]

Hi DNG,

Thank you, your amended code works really well, I can't thank you enough for all your advice and help on solving this issue.



Regards

Paul

 

[DotNetGnat]

no problem...glad to help...

hope you understood the fix...

-DNG

 

[kiwieur]

Hi DNG,

I Think so.

On the first iteration CStock = 0 therefore CStock = rsRunSum.Fields.Item("WHStock").Value.

after that CStock = CStock value from previous row.

would I be right in thinking that is correct



Regards

Paul

 

[DotNetGnat]

yes...you got the logic..

-DNG

 

[kiwieur]

DNG,

Just wanted to thank you again for your help in solving this issue, I know sometimes it must seem pretty simple to you Guru's out there but as always there is someone willing to help.

Cheers

Regards

Paul