applying payment to the earliest delinquency

  • Thread starter Thread starter DrEvil
  • Start date Start date
D

DrEvil

I'm trying to come up with some ideas how to apply payment to the earliest
delinquency first. I have a table called tblW2 and in it are fields: SSN,
YEAR, DUE among others and data might be formated this way:
YEAR: SSN: DUE:
2005 123 $50
2005 123 $80
2006 123 $100
2007 234 $150
Taxpayer might come in and wants to pay $125 and we would need to apply it
to the earliest delinquency first. I want to make this automated rather then
user calculated and i'm looking for best possible way to do this from
Payments form.
Possibly from VBA, that would probably be the optimal solution.

Thanks

Amir

Amir
 
Given the data example you provided, how do you expect Access to "know"
which one is the "earliest"? How do you know? (yes, I see that SSN = 123
has two in 2005 and one in 2006, but what if all were in 2005? It seems
like you need another field to hold an actual date, not just a year.)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You didn't give any data structure details, so this is a bit of a guess. You
can retrieve a recordset similar to what you list below, sorted by year.
Then in VBA loop through the recordset. If the amount due is less than the
remaining payment balance, mark it paid and reduce the payment balance. So
pseudo-code is something like:
strSQL = "Select Year, Due From MyTable Where SSN='" & ThePaymentSSN & "'
Order By Year"
Set rst = Currentdb.OpenRecordset(strSQL)
Do While !rst.eof And PaymentBalance > 0
If rst.fields("Due") <= PaymentBalance Then
AmountToPay = rst.fields("Due")
Else
AmountToPay = PaymentBalance
End If
PaymentBalance = PaymentBalance - rst.fields("Due")
rst.Edit
rst.fields("Due") = rst.fields("Due") - AmountToPay
rst.Update
rst.MoveNext
Loop

Then some code to deal with a possible remaining payment balance if the
person overpaid.
 
I doubt whether this can be done with a single update query.
Your logic needs to be to search for the earliest delinquent amount then
apply the payment to it. If there is payment left over, then search for the
next earliest delinguent amount then repeat the process until all of the
payment is applied. Its possible (though unlikely) they paid too much in
which case you need to decide what to do with the excess.
Seems like this could be achieved with a recordset loop in VB.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
First off this is not an easy task. If you are doing this as a learning
exercise or for your own use it's just time consuming.
If not then you should find an accounting program that allows linking to or
importating data and let that program handle it.

You will have to have a way to determine which is first as mentioned.
Then you need to apply what is paid across one or more payment fields that
match your due fields.
Suppose your records below are in order and 123 gives you $70.00
YEAR: SSN: DUE: Paid Balance
2005 123 $50 $50 0
2005 123 $80 $20 $60.
2006 123 $100
2007 234 $150

You need some way to keep track of that original $70.00, not a real problem
until he gives you another $50.00 and then $100.00 a while later.
Part of three payments will affect the second record in the list.

If you are using Access 2007 and understand what mulitvalued fields are in
fact this might be a good use for them. (Sigh, where are related multivalued
fields when you need them)
 
Thank you all for replying with your ideas.
This is not some kind of exercise or trying to learn MS Access, it's just a
small part of large database that will manage delinquencies, notices,
document imaging, contracts, calendars and so on. It's actually MS Access
with MySQL backend if someone is wondering.

In any case Payment would need to be applied according to YEAR value,
earliest year first. If there are multiple records of the same YEAR and same
SSN then it would be FIFO. I also had an ID(autonumber) field that
corresponds to YEAR field in that the earliest year has a smallest ID number.
Payment would be applied under subform sfrmPayments to the table called
tblPayments. I was thinking of storing amounts applied and to which year as
soon as I get over this part.

Here are 2 pictures with fake data to give some idea of what's going on.

http://img714.imageshack.us/img714/4783/mainscreendqs.png
http://img535.imageshack.us/img535/2274/mainscreenpayments.png
 
My question about the sequence to be applied if multiple records have the
same year still holds.

Access tables are "buckets o' data", with no (humanly-apparent) order. If
you don't specify a field on which to sort, Access gets to choose! Instead
of storing year (by the way, the word "year" is a reserved word in Access -
don't use it unless you want both you and Access to get confused), why not
store a date/time value? You can use THAT field for your sorting to find
your FIFO...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Actually field YEAR is really called LoadYear, I just simplified it in forum.
Reason why Date/Time field wouldn't work is because I get whole year or
filings from IRS on a single day so that particular year of records would
have same date/time stamp. Best option might be to use ID(autonumber) in
conjuction with LoadYear to get around those multi-records for single
year/SSN.
 
DrEvil -

You will want to get the database structure right on this one. You need a
table that will store the payments-applied-to-delinquencies. Something like
this:

tblPmtsByW2
PaymentID (foreign key to tblPayments)
DelinquencyID (foreign key to tblW2)
AmountApplied

This table will let you easily answer questions such as:
1. Which delinquencies did payment zzzz get applied to?
2. Which payments were applied to delinquency xxxx?

Now that the table is set up, you will need to write code that will apply
payments correctly. Here is a high-level structure:

Set up your variables, including recordset variables to allow you to open
the new table to add records and to loop through the delinquencies and update
them for the current SSN. Set up variables to hold remaining balance on the
current tblW2 record and the remaining payment.

Open the new table as a recordset so you can add new records.

Open a recordset of the tblW2 table for the given SSN, for records with a
balance, and in date order (you mentioned you could use LoadYear and an
AutoID field for this).

Loop through the records in the tblW2 recordset in the order of oldest
first, checking each record as follows:
If the balance on the record is greater than or equal to the remaining
payment, then apply the full remaining payment to this record. This means
add a record to the new table above, decrement the balance on this record,
and stop processing.

If the balance is less than the remaining payment, then apply the remaining
payment amount this record and decrement the remaining payment by this
amount. This means add a record to the new table above, decrement the
remaining payment by the balance due in the current record and then set the
new balance for this delinquency to zero.

Loop through the records until the remaining payment is zero or until there
are no more delinquent balances. If you hit the case where delinquent
balances are all zero first, then you may need to refund the remaining
payment.
 
That's exactly what my plan is, going about creating VBA code would be my
biggest problem. I'm going to be creating payments-applied-to-delinquencies
table once I resolve this VBA code in order to apply payments properly. I
need to have that table in any case but I wanted to tackle VBA problem first.

Here is what I got thus far with code from Paul:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
[Forms]![frmDelinquents]![SSN] & " ORDER BY tblw2.LoadYear, tblw2.ID;"

Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF And PaymentAmount > 0
If rst.Fields("Due") <= PaymentAmount Then
AmountToPay = rst.Fields("Due")
Else
AmountToPay = PaymentAmount
End If
PaymentAmount = PaymentAmount - rst.Fields("Due")
rst.Edit
rst.Fields("Due") = rst.Fields("Due") - PaymentAmount
rst.Update
rst.MoveNext
Loop

But I get an error about "data type mismatch in criteria expression" error
3464
Not sure if this is related to not having quotes around SSN criteria...
 
DrEvil -

Change rst.Fields("Due") to rst!Due to properly reference the
field in the recordset.

--
Daryl S


DrEvil said:
That's exactly what my plan is, going about creating VBA code would be my
biggest problem. I'm going to be creating payments-applied-to-delinquencies
table once I resolve this VBA code in order to apply payments properly. I
need to have that table in any case but I wanted to tackle VBA problem first.

Here is what I got thus far with code from Paul:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
[Forms]![frmDelinquents]![SSN] & " ORDER BY tblw2.LoadYear, tblw2.ID;"

Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF And PaymentAmount > 0
If rst.Fields("Due") <= PaymentAmount Then
AmountToPay = rst.Fields("Due")
Else
AmountToPay = PaymentAmount
End If
PaymentAmount = PaymentAmount - rst.Fields("Due")
rst.Edit
rst.Fields("Due") = rst.Fields("Due") - PaymentAmount
rst.Update
rst.MoveNext
Loop

But I get an error about "data type mismatch in criteria expression" error
3464
Not sure if this is related to not having quotes around SSN criteria...




Daryl S said:
DrEvil -

You will want to get the database structure right on this one. You need a
table that will store the payments-applied-to-delinquencies. Something like
this:

tblPmtsByW2
PaymentID (foreign key to tblPayments)
DelinquencyID (foreign key to tblW2)
AmountApplied

This table will let you easily answer questions such as:
1. Which delinquencies did payment zzzz get applied to?
2. Which payments were applied to delinquency xxxx?

Now that the table is set up, you will need to write code that will apply
payments correctly. Here is a high-level structure:

Set up your variables, including recordset variables to allow you to open
the new table to add records and to loop through the delinquencies and update
them for the current SSN. Set up variables to hold remaining balance on the
current tblW2 record and the remaining payment.

Open the new table as a recordset so you can add new records.

Open a recordset of the tblW2 table for the given SSN, for records with a
balance, and in date order (you mentioned you could use LoadYear and an
AutoID field for this).

Loop through the records in the tblW2 recordset in the order of oldest
first, checking each record as follows:
If the balance on the record is greater than or equal to the remaining
payment, then apply the full remaining payment to this record. This means
add a record to the new table above, decrement the balance on this record,
and stop processing.

If the balance is less than the remaining payment, then apply the remaining
payment amount this record and decrement the remaining payment by this
amount. This means add a record to the new table above, decrement the
remaining payment by the balance due in the current record and then set the
new balance for this delinquency to zero.

Loop through the records until the remaining payment is zero or until there
are no more delinquent balances. If you hit the case where delinquent
balances are all zero first, then you may need to refund the remaining
payment.
Click to expand...
Click to expand...
 
This is looking much better but I seem to be missing something, here is the
data that I'm trying to update:
Due Collected LoadYear
324.62 0 2005
257.83 0 2006
218.39 0 2006
448.92 0 2007

Here is the code:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
"'" & [Forms]![frmDelinquents]![SSN] & "'" & " ORDER BY tblw2.LoadYear,
tblw2.ID;"

Dim PaymentBalance As String
Dim AmountToPay As String

PaymentBalance = PaymentAmount
Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF Or PaymentBalance > 0
If rst.Due <= PaymentBalance Then
AmountToPay = rst.Due
Else
AmountToPay = PaymentBalance
End If
PaymentBalance = PaymentBalance - rst.Due
rst.Edit
rst.Due = rst.Due - AmountToPay
rst.Update
rst.MoveNext
Loop

It works great if amount of payment is less then first delinquency (324.62)
and it's having problem assigning money if it's over that amount. For example
I entered a payment of $400 and it apply's first payment perfectly (it zeros
out due amount) and goes to next amount 257.83 but it doesn't get stopped
here:
If rst.Due <= PaymentBalance Then
It says that due is $257.83 and that PaymentBalance is $75.38 (400-324.62)
which is all correct but instead of saying it's greater it continues on to:
AmountToPay = rst.Due
Which zeros out $257.83 instead of deducting from $75.38 from it.
Not sure why is that... any idea?

Amir



Daryl S said:
DrEvil -

Change rst.Fields("Due") to rst!Due to properly reference the
field in the recordset.

--
Daryl S


DrEvil said:
That's exactly what my plan is, going about creating VBA code would be my
biggest problem. I'm going to be creating payments-applied-to-delinquencies
table once I resolve this VBA code in order to apply payments properly. I
need to have that table in any case but I wanted to tackle VBA problem first.

Here is what I got thus far with code from Paul:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
[Forms]![frmDelinquents]![SSN] & " ORDER BY tblw2.LoadYear, tblw2.ID;"

Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF And PaymentAmount > 0
If rst.Fields("Due") <= PaymentAmount Then
AmountToPay = rst.Fields("Due")
Else
AmountToPay = PaymentAmount
End If
PaymentAmount = PaymentAmount - rst.Fields("Due")
rst.Edit
rst.Fields("Due") = rst.Fields("Due") - PaymentAmount
rst.Update
rst.MoveNext
Loop

But I get an error about "data type mismatch in criteria expression" error
3464
Not sure if this is related to not having quotes around SSN criteria...




Daryl S said:
DrEvil -

You will want to get the database structure right on this one. You need a
table that will store the payments-applied-to-delinquencies. Something like
this:

tblPmtsByW2
PaymentID (foreign key to tblPayments)
DelinquencyID (foreign key to tblW2)
AmountApplied

This table will let you easily answer questions such as:
1. Which delinquencies did payment zzzz get applied to?
2. Which payments were applied to delinquency xxxx?

Now that the table is set up, you will need to write code that will apply
payments correctly. Here is a high-level structure:

Set up your variables, including recordset variables to allow you to open
the new table to add records and to loop through the delinquencies and update
them for the current SSN. Set up variables to hold remaining balance on the
current tblW2 record and the remaining payment.

Open the new table as a recordset so you can add new records.

Open a recordset of the tblW2 table for the given SSN, for records with a
balance, and in date order (you mentioned you could use LoadYear and an
AutoID field for this).

Loop through the records in the tblW2 recordset in the order of oldest
first, checking each record as follows:
If the balance on the record is greater than or equal to the remaining
payment, then apply the full remaining payment to this record. This means
add a record to the new table above, decrement the balance on this record,
and stop processing.

If the balance is less than the remaining payment, then apply the remaining
payment amount this record and decrement the remaining payment by this
amount. This means add a record to the new table above, decrement the
remaining payment by the balance due in the current record and then set the
new balance for this delinquency to zero.

Loop through the records until the remaining payment is zero or until there
are no more delinquent balances. If you hit the case where delinquent
balances are all zero first, then you may need to refund the remaining
payment.

--
Daryl S


:

I'm trying to come up with some ideas how to apply payment to the earliest
delinquency first. I have a table called tblW2 and in it are fields: SSN,
YEAR, DUE among others and data might be formated this way:
YEAR: SSN: DUE:
2005 123 $50
2005 123 $80
2006 123 $100
2007 234 $150
Taxpayer might come in and wants to pay $125 and we would need to apply it
to the earliest delinquency first. I want to make this automated rather then
user calculated and i'm looking for best possible way to do this from
Payments form.
Possibly from VBA, that would probably be the optimal solution.

Thanks

Amir

Amir
Click to expand...
Click to expand...
Click to expand...
 
It was oversight on my part, I should have declared;
Dim PaymentBalance As Currency
Dim AmountToPay As Currency
and not as a string.
Once I fixed this part it's deducting perfectly no matter amount paid. Now I
need to figure how to assign this amount to Collected field and then
secondary table that would identify amount breakdowns.

Amir



DrEvil said:
This is looking much better but I seem to be missing something, here is the
data that I'm trying to update:
Due Collected LoadYear
324.62 0 2005
257.83 0 2006
218.39 0 2006
448.92 0 2007

Here is the code:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
"'" & [Forms]![frmDelinquents]![SSN] & "'" & " ORDER BY tblw2.LoadYear,
tblw2.ID;"

Dim PaymentBalance As String
Dim AmountToPay As String

PaymentBalance = PaymentAmount
Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF Or PaymentBalance > 0
If rst.Due <= PaymentBalance Then
AmountToPay = rst.Due
Else
AmountToPay = PaymentBalance
End If
PaymentBalance = PaymentBalance - rst.Due
rst.Edit
rst.Due = rst.Due - AmountToPay
rst.Update
rst.MoveNext
Loop

It works great if amount of payment is less then first delinquency (324.62)
and it's having problem assigning money if it's over that amount. For example
I entered a payment of $400 and it apply's first payment perfectly (it zeros
out due amount) and goes to next amount 257.83 but it doesn't get stopped
here:
If rst.Due <= PaymentBalance Then
It says that due is $257.83 and that PaymentBalance is $75.38 (400-324.62)
which is all correct but instead of saying it's greater it continues on to:
AmountToPay = rst.Due
Which zeros out $257.83 instead of deducting from $75.38 from it.
Not sure why is that... any idea?

Amir



Daryl S said:
DrEvil -

Change rst.Fields("Due") to rst!Due to properly reference the
field in the recordset.

--
Daryl S


DrEvil said:
That's exactly what my plan is, going about creating VBA code would be my
biggest problem. I'm going to be creating payments-applied-to-delinquencies
table once I resolve this VBA code in order to apply payments properly. I
need to have that table in any case but I wanted to tackle VBA problem first.

Here is what I got thus far with code from Paul:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
[Forms]![frmDelinquents]![SSN] & " ORDER BY tblw2.LoadYear, tblw2.ID;"

Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF And PaymentAmount > 0
If rst.Fields("Due") <= PaymentAmount Then
AmountToPay = rst.Fields("Due")
Else
AmountToPay = PaymentAmount
End If
PaymentAmount = PaymentAmount - rst.Fields("Due")
rst.Edit
rst.Fields("Due") = rst.Fields("Due") - PaymentAmount
rst.Update
rst.MoveNext
Loop

But I get an error about "data type mismatch in criteria expression" error
3464
Not sure if this is related to not having quotes around SSN criteria...




:

DrEvil -

You will want to get the database structure right on this one. You need a
table that will store the payments-applied-to-delinquencies. Something like
this:

tblPmtsByW2
PaymentID (foreign key to tblPayments)
DelinquencyID (foreign key to tblW2)
AmountApplied

This table will let you easily answer questions such as:
1. Which delinquencies did payment zzzz get applied to?
2. Which payments were applied to delinquency xxxx?

Now that the table is set up, you will need to write code that will apply
payments correctly. Here is a high-level structure:

Set up your variables, including recordset variables to allow you to open
the new table to add records and to loop through the delinquencies and update
them for the current SSN. Set up variables to hold remaining balance on the
current tblW2 record and the remaining payment.

Open the new table as a recordset so you can add new records.

Open a recordset of the tblW2 table for the given SSN, for records with a
balance, and in date order (you mentioned you could use LoadYear and an
AutoID field for this).

Loop through the records in the tblW2 recordset in the order of oldest
first, checking each record as follows:
If the balance on the record is greater than or equal to the remaining
payment, then apply the full remaining payment to this record. This means
add a record to the new table above, decrement the balance on this record,
and stop processing.

If the balance is less than the remaining payment, then apply the remaining
payment amount this record and decrement the remaining payment by this
amount. This means add a record to the new table above, decrement the
remaining payment by the balance due in the current record and then set the
new balance for this delinquency to zero.

Loop through the records until the remaining payment is zero or until there
are no more delinquent balances. If you hit the case where delinquent
balances are all zero first, then you may need to refund the remaining
payment.

--
Daryl S


:

I'm trying to come up with some ideas how to apply payment to the earliest
delinquency first. I have a table called tblW2 and in it are fields: SSN,
YEAR, DUE among others and data might be formated this way:
YEAR: SSN: DUE:
2005 123 $50
2005 123 $80
2006 123 $100
2007 234 $150
Taxpayer might come in and wants to pay $125 and we would need to apply it
to the earliest delinquency first. I want to make this automated rather then
user calculated and i'm looking for best possible way to do this from
Payments form.
Possibly from VBA, that would probably be the optimal solution.

Thanks

Amir

Amir
Click to expand...
Click to expand...
Click to expand...
 
DrEvil -

Great! Go back to my first posting on this for the main structure. After
you play around, if you need more help, start another new post with specific
questions on the new issues.

--
Daryl S


DrEvil said:
It was oversight on my part, I should have declared;
Dim PaymentBalance As Currency
Dim AmountToPay As Currency
and not as a string.
Once I fixed this part it's deducting perfectly no matter amount paid. Now I
need to figure how to assign this amount to Collected field and then
secondary table that would identify amount breakdowns.

Amir



DrEvil said:
This is looking much better but I seem to be missing something, here is the
data that I'm trying to update:
Due Collected LoadYear
324.62 0 2005
257.83 0 2006
218.39 0 2006
448.92 0 2007

Here is the code:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
"'" & [Forms]![frmDelinquents]![SSN] & "'" & " ORDER BY tblw2.LoadYear,
tblw2.ID;"

Dim PaymentBalance As String
Dim AmountToPay As String

PaymentBalance = PaymentAmount
Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF Or PaymentBalance > 0
If rst.Due <= PaymentBalance Then
AmountToPay = rst.Due
Else
AmountToPay = PaymentBalance
End If
PaymentBalance = PaymentBalance - rst.Due
rst.Edit
rst.Due = rst.Due - AmountToPay
rst.Update
rst.MoveNext
Loop

It works great if amount of payment is less then first delinquency (324.62)
and it's having problem assigning money if it's over that amount. For example
I entered a payment of $400 and it apply's first payment perfectly (it zeros
out due amount) and goes to next amount 257.83 but it doesn't get stopped
here:
If rst.Due <= PaymentBalance Then
It says that due is $257.83 and that PaymentBalance is $75.38 (400-324.62)
which is all correct but instead of saying it's greater it continues on to:
AmountToPay = rst.Due
Which zeros out $257.83 instead of deducting from $75.38 from it.
Not sure why is that... any idea?

Amir



Daryl S said:
DrEvil -

Change rst.Fields("Due") to rst!Due to properly reference the
field in the recordset.

--
Daryl S


:

That's exactly what my plan is, going about creating VBA code would be my
biggest problem. I'm going to be creating payments-applied-to-delinquencies
table once I resolve this VBA code in order to apply payments properly. I
need to have that table in any case but I wanted to tackle VBA problem first.

Here is what I got thus far with code from Paul:

strSQL = "SELECT tblw2.Due, tblw2.Collected FROM tblw2 WHERE [tblw2.SSN]=" &
[Forms]![frmDelinquents]![SSN] & " ORDER BY tblw2.LoadYear, tblw2.ID;"

Set rst = CurrentDb.OpenRecordset(strSQL)
Do While rst.EOF And PaymentAmount > 0
If rst.Fields("Due") <= PaymentAmount Then
AmountToPay = rst.Fields("Due")
Else
AmountToPay = PaymentAmount
End If
PaymentAmount = PaymentAmount - rst.Fields("Due")
rst.Edit
rst.Fields("Due") = rst.Fields("Due") - PaymentAmount
rst.Update
rst.MoveNext
Loop

But I get an error about "data type mismatch in criteria expression" error
3464
Not sure if this is related to not having quotes around SSN criteria...




:

DrEvil -

You will want to get the database structure right on this one. You need a
table that will store the payments-applied-to-delinquencies. Something like
this:

tblPmtsByW2
PaymentID (foreign key to tblPayments)
DelinquencyID (foreign key to tblW2)
AmountApplied

This table will let you easily answer questions such as:
1. Which delinquencies did payment zzzz get applied to?
2. Which payments were applied to delinquency xxxx?

Now that the table is set up, you will need to write code that will apply
payments correctly. Here is a high-level structure:

Set up your variables, including recordset variables to allow you to open
the new table to add records and to loop through the delinquencies and update
them for the current SSN. Set up variables to hold remaining balance on the
current tblW2 record and the remaining payment.

Open the new table as a recordset so you can add new records.

Open a recordset of the tblW2 table for the given SSN, for records with a
balance, and in date order (you mentioned you could use LoadYear and an
AutoID field for this).

Loop through the records in the tblW2 recordset in the order of oldest
first, checking each record as follows:
If the balance on the record is greater than or equal to the remaining
payment, then apply the full remaining payment to this record. This means
add a record to the new table above, decrement the balance on this record,
and stop processing.

If the balance is less than the remaining payment, then apply the remaining
payment amount this record and decrement the remaining payment by this
amount. This means add a record to the new table above, decrement the
remaining payment by the balance due in the current record and then set the
new balance for this delinquency to zero.

Loop through the records until the remaining payment is zero or until there
are no more delinquent balances. If you hit the case where delinquent
balances are all zero first, then you may need to refund the remaining
payment.

--
Daryl S


:

I'm trying to come up with some ideas how to apply payment to the earliest
delinquency first. I have a table called tblW2 and in it are fields: SSN,
YEAR, DUE among others and data might be formated this way:
YEAR: SSN: DUE:
2005 123 $50
2005 123 $80
2006 123 $100
2007 234 $150
Taxpayer might come in and wants to pay $125 and we would need to apply it
to the earliest delinquency first. I want to make this automated rather then
user calculated and i'm looking for best possible way to do this from
Payments form.
Possibly from VBA, that would probably be the optimal solution.

Thanks

Amir

Amir
Click to expand...
Click to expand...
Click to expand...
 
Back
Top