After playing with the code from the the link See http://www.dbazine.com/ofinterest/oi-articles/celko32 from Karl Fiderl this is what i ended up doing - not a perfect solution but it does enough to help the cashier. If there r ang glaring issues please respond and If anyone has solutions using vbscript and rowsets I would be interested to see them

This will create a list of Debits and Credits to be allocated. Obviously the final select statement must be changed to an UPDATE. Thanks to all for their suggestions

 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

         WHERE TABLE_NAME = 'vw_DebtorAlloc')

   DROP VIEW vw_DebtorAlloc

GO

Create view vw_DebtorAlloc as

SELECT W1.ACCNT_CODE,W1.TRANS_DATE,W1.CONV_CODE,

       SUM(CASE WHEN W2.TRANS_DATE <= W1.TRANS_DATE

                THEN W2.OTHER_AMT ELSE 0 END) as RunningTot

  FROM Sales AS W1,

       Sales AS W2

 WHERE (W1.ACCNT_CODE LIKE 'S%' AND W1.ALLOCATION NOT IN ('A', 'C', 'R', '1')) AND W2.TRANS_DATE <= W1.TRANS_DATE AND W2.ACCNT_CODE = W1.ACCNT_CODE

 GROUP BY  W1.ACCNT_CODE, W1.CONV_CODE, W1.TRANS_DATE

GO

SELECT S.ACCNT_CODE, S.TRANS_DATE, S.OTHER_AMT, S.ALLOCATION

FROM  Staff.dbo.Sales AS S

 INNER JOIN

  Staff.dbo.vw_DebtorAlloc AS L

 ON

 S.ACCNT_CODE = L.ACCNT_CODE AND S.TRANS_DATE <= L.TRANS_DATE AND L.RunningTot = 0