Running sum allocations

[mcgrcoAgain]

After a 5 year hiatus from ms access I find myself back using the tool in anger and struggling with something that would have been very basic back in the day

The problem I'm trying to solve for is as follows :

I have a two tables - one is cash with a term date and the other is collateral without a term date . The collateral is sorted in terms of good (1) to bad (5)

The principle is as follows . If I have two pieces of cash with different term dates I want to allocate the worse collateral to the cash with the longest term date.

So say had cash 100 with a term date of tomorrow and cash 50 and term next week. now lets say I had 50 in bad collateral and 100 in good. I would simple assign the 50 bad to 50 cash and the 100 good to the 100 cash. obviously this is simplified and you can have one piece of collateral that needs to be split.. let say 40 bad and 110 good. In this example I would allow the 40 to the 50 cash and 10 of the 110 . The issue is one record needs to be split and I dont know how to do it.

So what i do it I load the cash into a query in vba and sort it by date- latest date first - I then load the collateral into a query sorted by quality (bad to good). I then check the cash amount against the collateral amount and if its fully covered I assign the cash term to the collateral. At some point i get a collateral amount that is greater than the amount of unallocated cash so I need to split the record . its almost like a running sum loop.

Any idea or tips on how to do this>

Much appreciated for your help !

 

[MarkK]

I don't totally get it. Maybe it would help if you show some sample data. I know you've described it there verbally with some numbers, but how are the tables structured? And then maybe show a sample of how the data is stored in those tables, and then a sample of what the query needs to do, so what a correct result would look like given the raw data.

 

[mcgrcoAgain]

Just put up a noddy excel example.. It's hard to explain but in essence I need to take the dates from the cash record and add it to the collateral records based on me assigning the worse quality collateral first.. In the attached example you can see the cash and the collateral table... then I've shown the expected results . I had to split one of the collateral records as the the total of the collateral did not match the cash.. its basically like a running sum with the ability to split a record ... tried all the stuff with dsum but it hasnt worked.

Many thanks for your help!

lost.com...

 

[Cronk]

I don't see any attachment. I'd like to see the table structure. What happens if there are several cash records with the same term date? Aggregate the cash and collateral amounts? Also what do you want to do with the resulting information?