Logic Query

# Thread: Logic Query

1. Senior Member
Join Date
Dec 1969
Posts
1,849

## Logic Query

Allright guys, time to pick your brains. Starting development on an application, not sure the best way of doing this...<BR><BR>Trying to resolve debits and credits (One to many)<BR><BR>Say I have a \$30 credit to an account, the application needs to find like a \$15 and a \$15 debit, or a \$20 and a \$10 debit, and whatnot to account for the credit to the account.<BR><BR>(Money in, and money out) We have one to one relationships accounted for, but need to account for the one to many..<BR><BR>Any ideas as to how I should go about developing this???<BR><BR>-- Whol

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## And what do you do...

...if you have a \$36 credit and the following debits: \$20, \$10, \$5, \$3, \$1 ??<BR><BR>Or, worse, just \$20 and \$10 debits?<BR><BR>Or, worst of all, a \$1000 debit, only?<BR><BR>Are you saying you can only match *WHOLE* debits to whole credits? That&#039;s a pretty crazy accounting system, and most people would scream bloody murder about having to fork over \$36 when they have \$1000 in their account. *MOST* accounting systems simply lump together all credits and all debits and show a balance.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
1,849

## For like bank transactions...

Account is supposed to be zeroed.. Money in, money out... Any single credit, can match to a single debit, or many debits that add up to the credit.<BR><BR>-- Whol

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## SO then do *NOT* match...

...just get the total.<BR><BR>Sum of credits minus sum of debits. Period.<BR><BR>Suppose you had credits of \$10, \$10, \$10, \$10<BR>and debits of \$25, \$15.<BR><BR>With your (pardon me, but...) silly "matching" scheme, none of those could possibly cancel.<BR><BR>But if you simply add up the credits and add up the debits... presto, zero balance.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
1,849

## Its not my system of doing things...

And it makes no sence, because there are no keys matching debits to credits, and I say, yeah, why not just add, and be done with it, but I guess this is how the client wants it... <BR><BR>-- Whol

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Tell the client...

...he/she is a dork and an idiot and his/her customers will line up screaming.<BR><BR>But if you *have* to do it...<BR><BR>I don&#039;t see any way short of trial and error.<BR><BR>Order both the debits and credits from most to least.<BR><BR>Let&#039;s see...<BR><BR>&#060;%<BR>credits = Array( 27, 22, 13 )<BR>debits = Array( 20, 15, 5, 2, 2, 1, 1, 1 )<BR><BR>For c = 0 To UBound( credits )<BR> cred = credits(c)<BR> Response.Write "Credit " & cred & " has matches " & Matches( credits(c), "" ) & "&#060;P&#062;"<BR>Next<BR><BR>Function Matches( cr, sofar )<BR> Dim d, deb, temp<BR> For d = 0 To UBound( debits )<BR> deb = debits(d)<BR> If deb &#060;&#062; 0 Then<BR> If deb = cr Then<BR> debits(d) = 0<BR> Matches = sofar & " " & deb<BR> Exit Function<BR> End If<BR> If deb &#060; cr Then<BR> debits(d) = 0<BR> temp = Matches( cr-deb, sofar & " " & deb )<BR> If temp &#060;&#062; "" Then<BR> Matches = temp<BR> Exit Function<BR> End If<BR> End If<BR> End If<BR> Next<BR> Matches = "" <BR>End Function<BR>%&#062;<BR><BR>Untested, off the top of my head.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## No, that doesn't work...

Too simple. Have to find a way to preserve the state of the debits array when it&#039;s not used.<BR><BR>Still thinking.<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay...*FIXED*

Play with this. Try different combos of credits and debits.<BR><BR>I think it works for any combos.<BR><BR>*********************************** **<BR><BR>&#060;HTML&#062;&#060;BODY&#062;<BR>&#06 0;%<BR>credits = Array( 27, 22, 13 ) <BR>debits = Array( 20, 15, 5, 5, 4, 4, 2, 2, 2, 1, 1, 1, 1, 1 ) <BR><BR>For c = 0 To UBound( credits ) <BR> cred = credits(c) <BR> m = Matches( credits(c), "" )<BR> If m &#060;&#062; "" Then<BR> Response.Write "Credit " & cred & " has matches " & m & "&#060;BR/&#062;"<BR> Else<BR> Response.Write "Credit " & cred & " has no matches in the debits" & "&#060;BR/&#062;"<BR> End If<BR> Response.Write "Remaining debits: " & Join(debits) & "&#060;P&#062;" & vbNewLine<BR>Next <BR><BR><BR>Function Matches( cr, sofar ) <BR> If cr = 0 Then<BR> Matches = sofar<BR> Exit Function<BR> End If<BR> Dim d, deb, temp, saveDebits <BR> For d = 0 To UBound( debits ) <BR> deb = debits(d) <BR> If deb &#060;&#062; 0 Then <BR> If deb &#060;= cr Then <BR> saveDebits = debits<BR> debits(d) = 0 <BR> temp = Matches( cr-deb, sofar & " " & deb ) <BR> If temp &#060;&#062; "" Then <BR> Matches = temp <BR> Exit Function <BR> End If <BR> debits = saveDebits<BR> End If <BR> End If <BR> Next <BR> Matches = "" <BR>End Function <BR>%&#062;<BR>&#060;/BODY&#062;&#060;/HTML&#062;<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•