Updating a table

[Guest]

I have been struggling with a problem, please help

I have a table called Collections. This table contains Customer Info, and more importantly dollar amounts that were charged off and taken as a loss by my company. The amounts are broken down into catagories. If a customer did not pay the invoice of $100.00, they may have late charges of $25.00, and Interest of $10.00. So they owe $135.00 in total, but for accounting purposes and General Ledger requirements the amounts are tracked separately.

I have a second table for payments. How can I apply these payments to the Collections table and reduce the figures as they pay? I was considering an Update query, but I keep getting stuck. I cannot figure out the Expression needed to apply the payments against the collections, and reduce the figure

Secondly, I want to recover first the Invoice amount, then the Late Charges, then the Interest. How do I in my expression specify to move from one category or another. For instance, If my customer pays $115.00 that will take care of the Invoice and then part of the Last charges. Can I get a query to be that intelligent?

 

[David Mann]

I have been struggling with a problem, please help.

I have a table called Collections. This table contains Customer Info, and

more importantly dollar amounts that were charged off and taken as a loss by
my company. The amounts are broken down into catagories. If a customer did
not pay the invoice of $100.00, they may have late charges of $25.00, and
Interest of $10.00. So they owe $135.00 in total, but for accounting
purposes and General Ledger requirements the amounts are tracked separately.

I have a second table for payments. How can I apply these payments to the

Collections table and reduce the figures as they pay? I was considering an
Update query, but I keep getting stuck. I cannot figure out the Expression
needed to apply the payments against the collections, and reduce the figure.

Secondly, I want to recover first the Invoice amount, then the Late

Charges, then the Interest. How do I in my expression specify to move from
one category or another. For instance, If my customer pays $115.00 that
will take care of the Invoice and then part of the Last charges. Can I get
a query to be that intelligent?

Question: Do you want to reduce the amounts in the Collections table as you
receive payments? If yes, how will you know that the payments have been
allocated? - you may need a field in the Payments table to record this.

Question: Do you simply want a report that takes the data from both tables
and shows you the net amount owing?

Either way you are going to want some code to look through the records and
allocate the payments to the right categories. It might be possible to do
what you want with queries, but it looks like you will need code.

David

 

[David Mann]

In answer to the first question, Yes as payments are recieved they are

recorded in the payments table.

I do want to get a report of some type. My intent is to show the end

result on a "form", so when the next payment comes in the person processing
it knows what categorie it goes to based on which has a balance.

I keep trying differenct queries and just cannot get it. Any suggestions are helpful



----- David Mann wrote: -----


Info, and
more importantly dollar amounts that were charged off and taken as a loss by
my company. The amounts are broken down into catagories. If a customer did
not pay the invoice of $100.00, they may have late charges of $25.00, and
Interest of $10.00. So they owe $135.00 in total, but for accounting
purposes and General Ledger requirements the amounts are tracked separately. payments to the
Collections table and reduce the figures as they pay? I was considering an
Update query, but I keep getting stuck. I cannot figure out the Expression
needed to apply the payments against the collections, and reduce the figure. Late
Charges, then the Interest. How do I in my expression specify to move from
one category or another. For instance, If my customer pays $115.00 that
will take care of the Invoice and then part of the Last charges. Can I get
a query to be that intelligent?

Question: Do you want to reduce the amounts in the Collections table as you
receive payments? If yes, how will you know that the payments have been
allocated? - you may need a field in the Payments table to record this.

Question: Do you simply want a report that takes the data from both tables
and shows you the net amount owing?

Either way you are going to want some code to look through the records and
allocate the payments to the right categories. It might be possible to do
what you want with queries, but it looks like you will need code.

David

Your first problem is organising the data storage.
I guess you have separate fields in your [Collections] table for [Invoice],
[Charges] and [Interest].

The data might be easier to handle if you used the same field [Charge] for
all types of charge, and had a separate field [ChargeType] which tells you
what type of charge it is. This would give you an easy way of getting the
balance owing, just by summing the values in one table and grouping by
[Customer]. It would also allow you to add different [ChargeTypes] in future
if you need them, without restructuring your data. You could then do away
with your [Payments] table, because payments are just another type of charge
(albeit negative). However, let's ignore that for now...

The next step is to make a query with separate fields for [Invoice],
[LateCharges], [Interest] and [Payments].

If you want to allocate [Payments] to particular [ChargeType]s in sequence,
you can do this with calculated fields in your query, e.g.:

[InvoiceBalance] = [Invoice] - [Payments], but not less than zero, i.e.

[InvoiceBalance] = iif([Invoice]-[Payments]<0,0,[Invoice]-[Payments])

[LateChargesBalance] = [LateCharges] + [Invoice] - [Payments],
but not more than [LateCharges] and not less than zero.

[InterestBalance] = [Interest] + [LateCharges] + [Invoice] - [Payments],
but not more than [InterestBalance] and not less than zero.

These balance fields can appear on your form or in a report.
Whoever enters the data does not have to allocate the [Payment] to any
particular
type of charge, because the query will show the result of your
pre-determined allocation priority.

Sorry if that's as clear as mud, but it's getting late.

David