Producing Customers Statements

  • Thread starter Thread starter Paul ML
  • Start date Start date
P

Paul ML

I'm currently struggling with producing statements for our many
account customers and would be grateful for a little further help.
My problem is not related to actually producing accurate statement
reports, but rather to limiting the results that appear on these
reports.
For example, a customer may have say ten invoices (debits) in a month.
By the time the customer gets his statement in the post relating to
these 10 debits, he may have had another 2 or 3 debits against his
account. (not shown on the statement)
When he sends payment, he will probably send sufficient to cover the
first ten transactions ie: those that appeared on his statement.

The question is this:
How can i restrict his next statement so that the debits which have
since been paid for do not appear? This is a new database which i have
been developing for some time. It has been live since April 2003 and i
always knew this would become an issue. Busy customers are now getting
3 A4 sheets containing mostly historical and useless information from
months ago.

My apologies for the use of the "he" word when referring to customers.
Used for convenience only.
As always any enlightenment would be most gratefully received.
Thank you
 
Do you mark Invoices "paid" when a payment is applied? That
would be an easy way to eliminate the paid ones and send out only those
unpaid.
Click to expand...


John
Thank you very much for your advice.
I, being a novice, did not spot the obvious as you suggest....... mark
the paid invoices as paid.
It's easy to get wrapped up in complicated stuff whilst the simple
answer is missed. I will now look at ways to mark these as paid when
the payments arrive.
I think I just need to be able to show invoices outstanding on a
receipts form and maybe have a check box on that form to mark a new
"paid" field as "true"? eg: Orders.Paid=True
I can maybe foresee a problem when linking this form to the "Orders"
table, but I'd like to have this kind of method i think, as it may be
more easy for the users. As opposed to having them actually find an
actual invoice and mark it as paid from within the orders form.

Any further suggestions as to the validity or methods regarding this
would be welcomed.

Sorry for the very late response and thanks again.
 
Paul-

If you have credits and debits, then you probably should not let the user
set the "Paid" status. Or, if you do let them set it, then add code to
verify that the current order is, in fact, fully paid (sum of the credits >=
sum of debits). The preferable way would be to check each time they add a
payment and then mark the appropriate order "paid" when the correct total is
reached. You could also disallow posting of any further debits or credits
to an order marked paid - or automatically mark it unpaid if they change
anything.

Keep in mind that using this technique actually involves storing a
calculated value in the database. The "Paid" flag should be set ONLY when
payments match the amount owed. You have to add specific code to verify
this fact at every opportunity, or you risk having the paid flag set when it
shouldn't be or vice-versa. I think it's worth it in this case because the
query to sum and match credits and debits could be really ugly and slow when
you need to print invoices. As I recommend in my books, creating a
calculated value like this is OK as long as you understand all the
ramifications and you're doing it for a specific reason - usually
performance.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top