Geek Answers Handbook

Comparison of database entries by dollar

I have a debit table and a loan table. I need to try to match them as close as possible.

So, here is my idea - but I'm not the best with coding logic ... If anyone has a better idea, please feel free to call.

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 is my thought process:

  • Open the credit record set and collect DESCENDING for the loan amount.
  • Open a set of records for debits and sort ASCENDING by debit amount.
  • Move through the set of debit entries, one on one - because for each debit we need to match a loan that is equal to EQUAL TO or GREATER than the amount of the debit transfer.
  • Go through the set of credit records, one by one - this is how we get a loan equal to or greater than
  • , , . , LARGER, , , , .

, ...

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

- , . , - , MS Access ...

+4
6

. , , , . , . , .

, , . (m credits n debits m * n . , , )

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

. , , . , , . , , , , . , , , .. , , , , , , /, .

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

Public Type Record
    ID As Long
    Amount As Currency
    Allocated As Boolean
    AllocatedAmount As Currency
End Type

Public Function PaymentAllocation()
    Dim cr() As Record
    Dim de() As Record
    ' function returns records with amounts in descending order
    cr = QueryToArray("CR", "CR_AMT")
    de = QueryToArray("DE", "DE_AMT")

    ' first fit descending order
    ' starting with largest credit and debit, check if they fit
    ' if fit, then allocate
    ' if not, then check next credit
    Dim i As Long, j As Long
    For i = LBound(cr) To UBound(cr)
        For j = LBound(de) To UBound(de)
            If de(j).Allocated = False Then
                If (cr(i).Amount - cr(i).AllocatedAmount) >= de(j).Amount Then
                    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) As Record()
    Dim rs As Recordset
    Dim ans() As Record
    Dim i As Long
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & tableName & " ORDER BY " & tableName & "_AMT DESC;", dbOpenDynaset)
    ReDim ans(1 To DCount(tableName & "_ID", tableName))
    i = 0
    Do Until rs.EOF
        i = i + 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
+3

tblDebits

id     amount    credit_id
1      $1.50
2      $1.33
3      $2.55
4      $2.66

tblCredits

id     amount
1      $5.00
2      $3.55
3      $1.55
4      $1.35
5      $1.33

-:

Function AssignCredits()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim blnCreditAssigned As Boolean

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT tblDebits.id, tblDebits.amount, tblDebits.credit_id FROM tblDebits ORDER BY tblDebits.amount ASC")
    Set rs2 = db.OpenRecordset("SELECT tblCredits.id, tblCredits.amount FROM tblCredits ORDER BY tblCredits.amount ASC")

    rs1.MoveFirst
    rs2.MoveFirst

    Do Until rs1.EOF
        Do Until blnCreditAssigned = True Or rs2.EOF
            If rs2![amount] >= rs1![amount] Then
                rs1.Edit
                rs1![credit_id] = rs2![id]
                rs1.Update
                blnCreditAssigned = True
            End If   
            rs2.MoveNext
        Loop
        rs1.MoveNext
        blnCreditAssigned = False
    Loop

    rs1.Close
    rs2.Close
    MsgBox "Credit assignment complete"

End Function
0

, , , , . , .

Sub CreditMatch()
    Dim rsDebit As Recordset
    Dim rsCredit As Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM tblDebits ORDER BY Amount"
    Set rsDebit = CurrentDb.OpenRecordset(strSQL)
    Do
        strSQL = "SELECT * "
        strSQL = strSQL & "FROM tblCredits "
        strSQL = strSQL & "WHERE ID Not In (SELECT CreditID FROM tblDebits) AND Amount >=" & rsDebit("Amount")
        strSQL = strSQL & "  ORDER BY Amount"
        Set rsCredit = CurrentDb.OpenRecordset(strSQL)
        If Not rsCredit.EOF Then
            rsDebit.Edit
            rsDebit("CreditID") = rsCredit("ID")
            rsDebit.Update
        End If
        rsDebit.MoveNext
    Loop Until rsDebit.EOF
End Sub
0

- , :

select debit_value, min(credit_value) as match_credit_value
from
(
select d.value as debit_value, c.value as credit_value, d.id as did, c.id as cid
from debits d
cross join credits c
where c.value >= d.value and d.id < c.id
) x
group by x.debit_value

+-------------+--------------------+
| debit_value | match_credit_value |
+-------------+--------------------+
| 1.33        | 1.35               |
| 1.5         | 1.55               |
| 2.55        | 3.55               |
| 2.66        | 5                  |
+-------------+--------------------+

, SQL script:

create table debits ( id integer, value money );
insert into debits values (1, 1.33);
insert into debits values (2, 1.50);
insert into debits values (3, 2.55);
insert into debits values (4, 2.66);
create table credits ( id integer, value money );
insert into credits values (1, 1.33);
insert into credits values (2, 1.35);
insert into credits values (3, 1.55);
insert into credits values (4, 3.55);
insert into credits values (5, 5.00);
0

, , . . , , (, ). , , , .

  • $1.50
  • $5,00 ( )
  • if (debit >= credit) debit - credit - = > debitRemainder
    if (debit < credit) credit - debit - = > creditRemainder
  • debitRemainder > 0, 2
  • creditRemainder > 0, 1 ( step2, )

/, /, :

debits                 - credits
$1.50 -> $0.00         - ($5.00) $5.00 -> $3.50
$1.33 -> $0.00         - ($5.00) $3.50 -> $2.17
$2.55 -> $0.38         - ($5.00) $2.17 -> $0.00
($2.55) $0.38 -> $0.00 - ($3.55) $3.55 -> $3.17
$2.66 -> $0.00         - ($3.55) $3.17 -> $0.51
no more debits thus account has credit balance:
$0.51 + $1.55 + $1.35 + $1.33
0

- , ( , ?), , , , (credit-debit >= 0) ( customerID, , creditDate >= debitDate), .

I assume that you have a different table, because a loan can be applied to more than one debit, or a debit can be paid by more than one loan, but you did not mention this. Despite this, if I did this (and I understand what you are trying to do), I would start by joining the credit and debit amounts, so exact matches must be made first. You can then exclude them from iterative checks on credit and debit lines.

0
source

More articles:


All Articles