Ranking/Sorting across fields

[jkitzy]

Here's an interesting problem,

I have several fields in a table that represent portions of a larger debt.
Let's call them

Principal = 100
Fees = 50
LateCharges = 75
MiscFees = 25

A customer makes payments on a schedule

Payment 1 = 85
Payment 2 = 85
Payment 3 = 80

The order of payments should be:

Payment 1 = 85 principal
Payment 2 = 15 principal, 70 LateCharges
Payment 3 = 5 LateCharges, 50 Fees, 25 MiscFees

How do I rank these so that as I receive the payments, I can credit the
appropriate accounts for the portion of the payment received? The various fee
types can be in any order of highest to lowest. In other words, Fees may be
bigger that LateCharges, etc. or not.

Any help? Is this even clear?

Thanks.

--jkitzy

 

[Steve Schapel]

Jkitzy,

The first thing to notice is that there is a fundamental error in your
data structure. These debt accounts should not be separate fields.
Each one should be a separate record. Is your table design set in
concrete, or are you able to fix it?

Either way, if you mean you want to be able to enter a payment, and have
Access automatically distribute the payment to the accounts using the
cascading system you have illustrated, there is really no
straightforward way of doing this. It would be possible with a macro,
but it would be awkward. It would be relatively easy to write a
User-Defined Function using VBA. You will need to construct your
payments table appropriately, so that each payment can be segmented to
an account.

 

[jkitzy]

Thanks for responding Steve. I'm not sure what you mean by making separate
records for each fee account. Let me be a little more explicit with my
construction so far and perhaps you can advise me. I'm not a database expert,
so I may be way off on how to do what I'm doing. Here's my basic table
structure (there are more fields in each table, unrelated to this problem).
Field descriptions are in [brackets] for clarity:

Table: CustomerInfo
CID [sequence number - linked to other tables]
....[omitted customer address/name/business fields]
ACCT [Account Number]

Table: ChargeOffs
CID [customer ID linked to CustomerInfo table]
CO [sequence number for Charge Off]
COD [date of event]
TCO [Total Loss $]
PCO [Principal Loss $]
NSF [Loss - Fee Category 1 $]
SCF [Loss - Fee Category 2 $]
DOD [Loss - Fee Category 3 $]
OTF [Loss - Fee Category 4 $]

Table: RepaymentPlan
RAN [New Account Number]
CID [Relates to CustomerInfo Table]
PRD [Product Code]
CO [Relates to ChargeOffs Table]
RAD [Agreement Date]
NP [Number of payments agreed upon]
P1D [Payment Date]
P2D [Payment Date]
P3D [Payment Date]
P4D [Payment Date]
P1A [Payment $ Due]
P2A [Payment $ Due]
P3A [Payment $ Due]
P4A [Payment $ Due]

Table: RecoveryPayments
TID [sequence number - transaction ID]
CID [relates to CustomerInfo Table]
CO [relates to ChargeOffs Table]
PNO [Payment Number (1 - 4)]
PMD [Payment Date]
PMA [Payment $]
PAPT [Payment Allocated to Principal Account $]
PANT [Payment Allocated to Fee Account 1 $]
PAST [Payment Allocated to Fee Account 2 $]
PADT [Payment Allocated to Fee Account 3 $]
PAOT [Payment Allocated to Fee Account 4 $]

Basically, I'm entering the fee amounts, calculating a payment plan with
customizable dates and amounts due, and distributing the payments on receipt
to the appropriate recovery accounts as needed.

So, the CustomerInfo table is populated first, then the ChargeOffs. If the
customer agrees to repay, the RepaymentPlan table is populated and the
payments are received in the Recovery Table. make sense?

Is there a better structure?

 

[Steve Schapel]

Jkitzy,

Thanks for the further information.

I would certainly recommend some changes, according to the age-old adage
"fields are expensive, records are cheap".

For example, something along these lines...

Table: ChargeOffs
CID [customer ID linked to CustomerInfo table]
CO [sequence number for Charge Off]
COD [date of event]

Table: ChargeOffDetails
COID [Autonumber ID for each CO item]
CO [ChargeOff number linked to ChargeOffs table]
LossAmount
LossCategory

Table: RepaymentPlan
RAN [New Account Number] (is this a unique identifier for the
RepaymentPlan?)
xx CID xx (remove this field, it is invalid, as the CO field
automatically identifies the customer)
PRD [Product Code]
CO [Relates to ChargeOffs Table]
RAD [Agreement Date]

Table: RepaymentPlanPayments
RPPaymentID
RAN [Links to RepaymentPlan table]
PD [Payment Date]
PA [Payment $ Due]

Table: RecoveryPayments
TID [sequence number - transaction ID]
COID [Related to ChargeOffDetails table, will identify the account]
PMD [Payment Date]
PMA [Payment $]

Get the idea?

--
Steve Schapel, Microsoft Access MVP

Thanks for responding Steve. I'm not sure what you mean by making separate
records for each fee account. Let me be a little more explicit with my
construction so far and perhaps you can advise me. I'm not a database expert,
so I may be way off on how to do what I'm doing. Here's my basic table
structure (there are more fields in each table, unrelated to this problem).
Field descriptions are in [brackets] for clarity:

Table: CustomerInfo
CID [sequence number - linked to other tables]
...[omitted customer address/name/business fields]
ACCT [Account Number]

Table: ChargeOffs
CID [customer ID linked to CustomerInfo table]
CO [sequence number for Charge Off]
COD [date of event]
TCO [Total Loss $]
PCO [Principal Loss $]
NSF [Loss - Fee Category 1 $]
SCF [Loss - Fee Category 2 $]
DOD [Loss - Fee Category 3 $]
OTF [Loss - Fee Category 4 $]

Table: RepaymentPlan
RAN [New Account Number]
CID [Relates to CustomerInfo Table]
PRD [Product Code]
CO [Relates to ChargeOffs Table]
RAD [Agreement Date]
NP [Number of payments agreed upon]
P1D [Payment Date]
P2D [Payment Date]
P3D [Payment Date]
P4D [Payment Date]
P1A [Payment $ Due]
P2A [Payment $ Due]
P3A [Payment $ Due]
P4A [Payment $ Due]

Table: RecoveryPayments
TID [sequence number - transaction ID]
CID [relates to CustomerInfo Table]
CO [relates to ChargeOffs Table]
PNO [Payment Number (1 - 4)]
PMD [Payment Date]
PMA [Payment $]
PAPT [Payment Allocated to Principal Account $]
PANT [Payment Allocated to Fee Account 1 $]
PAST [Payment Allocated to Fee Account 2 $]
PADT [Payment Allocated to Fee Account 3 $]
PAOT [Payment Allocated to Fee Account 4 $]

Basically, I'm entering the fee amounts, calculating a payment plan with
customizable dates and amounts due, and distributing the payments on receipt
to the appropriate recovery accounts as needed.

So, the CustomerInfo table is populated first, then the ChargeOffs. If the
customer agrees to repay, the RepaymentPlan table is populated and the
payments are received in the Recovery Table. make sense?

Is there a better structure?

 

[Steve Schapel]

Jkitzy,

You might find this interesting...
http://support.microsoft.com/?id=283878

 

[jkitzy]

Thanks a million. I'll reconstruct this and let you know how I do. I really
appreciate the help.