Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > Courses > Access > Developer > D07 > < D06 | D08 >
Back to Access Developer 7    Comments List
Payment for many invoices Upload Images   Link   Email  
Khalid Mahdhar     
16 months ago
Is there an expansion for this lesson to cover the situation of one payment for multiple orders/invoices?
Kevin Yip       
16 months ago
Each payment not only can pay for multiple invoices, but multiple payments can also pay for one invoice.  And you need to decide what portion of a payment to apply to which outstanding invoice(s).  Obviously, you need to apply it to the outstanding invoice(s) with the closest due date(s).  There are a lot of issues to consider, both on the business side of things, and technical side.  For starters, I would consider a table structure like this:

PaymentID     InvoiceID     AmountApplied
1000          234           $1000.00
1000          567           $2733.01
1001          453           $123.27
1001          1734          $31743.90
1002          234           $123.45
1002          789           $300.34
1003          573           $6723.81

This table allows one payment to be applied to multiple invoices, as well as multiple payments to one invoice.  E.g. Payment 1000 and 1002 are both applied to Invoice 234.  Also, Payment 1000 is applied to 2 invoices, 234 and 567.

You also need to determine if an invoice is fully paid or not, which the table above doesn't tell you.  You have to do (probably lots of) queries to find that out.  If an invoice is fully paid, you "close" it.  From my experience, an invoice *doesn't* have to be fully paid to be "closed."  If a customer disputes the charge and refuses to pay, and the bank (or any arbitrator) decides the customer is right, then the customer doesn't have to pay the invoice in full, and will issue you a "chargeback" (essentially a "negative" invoice amount) which you will then need to apply to your database to zero out the invoice and "close" it.

This is a far bigger project than I can describe in just one post, so you may ask me some more questions (because I don't think Richard has a video on this).
Khalid Mahdhar     
16 months ago
Thanks, but developer 7 does not explain assigning receipts to invoices. Furthermore, I need explanation on how to set up the tables (probably many-to-many) and field goes to which table.

I can depict from you explanation that you are suggesting to use PaymentID, InvoiceID and AppliedAmount as field in the junction table, while still have other tables for the invoice information and another for the Payment Information.
Kevin Yip       
16 months ago
My table suggestion is a way to apply payments to invoices -- by adding relationships between them.

This is more than just assigning payments, though.  As I said, this is a complicated matter, and different companies have different needs and methods for this.  The accounting person in your company (if you aren't one yourself) may have a clearer idea of what needs to be done here.  This may involve a lot of accounting practices, a lot more than just applying payments to invoices.  You need to lay out all the requirements before you can even make the first step.  As I alluded to in my previous post, you may need a chargeback system, a way to determine if an invoice is "open" or "closed", etc.

No course can explain everything explicitly for every job and industry.  Most of the times you have to "adapt" from what you learned from courses.  If you have a solid foundation of Access knowledge, you can "adapt" to anything.  

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer 7.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/15/2024 4:17:47 PM. PLT: 1s