sql - Database RecordSet Matching on Dollar amounts -

i have table of debits , table of credits. need try , match them closely possible.

so here idea - i'm not best coding logic... if has better idea please feel free chime in.

table 1 - debits $1.50 $1.33 $2.55 $2.66  table 2 - credits $5.00 $3.55 $1.55 $1.35 $1.33 

so here thought process:

1. open recordset credits , sort descending credit amount.
2. open recordset debits , sort ascending debit amount.
3. move through debit recordset, 1 one - because each debit need match credit equal or greater debit amount
4. move through credit recordset, 1 one - how credit equal or greater the
5. if debit amount equals credit amount, stop , mark credit amount taken debit. if debit amount gets credit amount larger debit amount, stop , take first 1 greater than.

so in case of code example above... debits take following credits

$1.50 -> $1.55 $1.33 -> $1.33 $2.55 -> $3.55 $2.66 -> $5.00 

if can build code logic appreciated. unless i'm going wrong , should try else ms access can do...

this sounds bin packing problem. depending on number of records , how fast need solve determine algorithm can use. smaller number of records, can brute force find optimal solution. if insanely large heuristic based algorithm better.

basically exact implementation depend on how many combinations of records can made. (m credits , n debits means m*n combinations. imagine start optimizing removing exact matches)

http://en.wikipedia.org/wiki/bin_packing_problem

edit

the results of first fit descending. nice thing can fit more 1 debit 1 credit if fits. if don't want behavior modifiable. instead of checking if there enough space left, check if used or not. there other heuristics can use, best fit, worst fit, etc. in scenario, try , allocate exact matches first, run algorithm on remainder, mentioned in comment earlier, situation/solution depends on data set size.

$5.00 -> $2.66, $1.50 $3.55 -> $2.55 $1.55 -> $1.33   public type record     id long     amount currency     allocated boolean     allocatedamount currency end type  public function paymentallocation()     dim cr() record     dim de() record     ' function returns records amounts in descending order     cr = querytoarray("cr", "cr_amt")     de = querytoarray("de", "de_amt")      ' first fit descending order     ' starting largest credit , debit, check if fit     ' if fit, allocate     ' if not, check next credit     dim long, j long     = lbound(cr) ubound(cr)         j = lbound(de) ubound(de)             if de(j).allocated = false                 if (cr(i).amount - cr(i).allocatedamount) >= de(j).amount                     de(j).allocated = true                     cr(i).allocatedamount = cr(i).allocatedamount + de(j).amount                     debug.print cr(i).amount, de(j).amount                 end if             end if         next     next end function  public function querytoarray(tablename, fieldname) record()     dim rs recordset     dim ans() record     dim long     set rs = currentdb.openrecordset("select * " & tablename & " order " & tablename & "_amt desc;", dbopendynaset)     redim ans(1 dcount(tablename & "_id", tablename))     = 0     until rs.eof         = + 1         ans(i).id = rs(tablename & "_id").value         ans(i).amount = rs(fieldname).value         ans(i).allocated = false         rs.movenext     loop     rs.close     set rs = nothing     querytoarray = ans end function