Conundrum

[Mcgrco]

Ive been trying to do this for a week now and the dealine is looming so I'm really starting to panic abit . Im sure its a case of not being able to see the wood for the trees but I need help in a real hurry.


I have attached a table with some information in it. Here is the problem.

I have a list of items with Nominal amounts attached. There can be two of the same items the only differnce being the settlement date.
I also have a column with an adjustment amount. There will only ever be one adjustment amount per actual item (no grouping by settlement date) so if an item has duplicated records the average Adjustment amount should cope with this. What I need to do is take the average adjustment and working back from the latest date pull back enough of the items to equal the adjustment . The key here the settlement date


i.e In this case
ITEM Nominalamounts Adjustment Adjustment_Nom settlement date
US3379321074 (276,700) (3,850,000) 50 14-Feb-03
US3379321074 (3,800,000) (3,850,000) 3,800,000 19-Feb-03


There are two item with differnt settlement days. The adjustment for ID US3379321074 is the average of sum of adjustment ie -3,850,000. Starting at the item with the furthest date (19th). I need to aportion the adjustment out in realation to the nominal amount untill the adjustment is used but it must start from the furthest date.




Any help is greatly appeciated

*

 

[FoFa]

I would use a query (SQL) that returns your data for one settlement (or sorted by settlement) and in descending date order. Grab the average of sum of adjustment to hold and work with. Than you can just loop through the rows handling them how ever you need to.
OR I don't understand.........

 

[Mcgrco]

Thanks Fofa.

I sort of got around it..

The problem I had with sql was i needed to select the grouped items. Then using that selection open the table again, scroll through the records and edit details. The two sql statements conflicted as they were looking at the same table. To get around this I loaded the grouped items into an array and closed the link to the table. I then used the array o loop throught and edit the table.
Probably not the best way to do it but it works.

Cheers