this solution from Ken worked. Thanks Ken

thank you very much for giving your precious time in helping me with this problem.....special thanks to Ken, Bledu, and Ben.....i'll keep you posted if it works! thanks guys.

sorry for replying just now.....the hier_level is based on the predecessor columns so if the predecessor is blank it is assumed that it is level 1

thanks camilo

thank you very much for the help.....it's now working

Here's an example:

CURRENT

ID     AmtType     Amt_1    Amt_1_desc    Amt_2    Amt_2_desc    Amt_3    Amt_3_desc

1     Risk             100        RISK_1         200          Rsk_2          300         desc_3

2     Opportunities 600        Desc_1         890          Opp_2         700         Opprt desc

3     Acct Balance   10        Balance          50           Bal_amt       ...