Results 1 to 7 of 7
  1. #1
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63

    How to get latest Receivable Amount?

    I have most of the customers who do not pay me the full payment against the invoice total amount. Instead, they pay partial payments.



    And the invoice total is never a final total because there are some products in the line items against which I am paid full or partial payment. That's why, the remaining (calculated) prices of most of the products changes automatically from time to time in the reports.

    So I need a continuous invoices and continuous payments system.

    I can manage the latest invoice total by calculating the line items of a specific customer, and I have created a payment table (with CustomerID) to record the received payments but I do not have an idea of how to relate the payments with invoices and getting the latest Receivable Amount.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Since a Customer can have many invoices, you will need to record the InvoiceID as well as the CustomerID on the payment record. In fact, you would only need the InvoiceID on the payment record, since the Invoice record would have the Customer ID on it.

  3. #3
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    But I create invoices from "Orders" and "Order Details" tables using QUERIES, I do not have "Invoice" table. How can I use InvoiceID without "Invoice" table?

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Well, since you talked about invoices and invoice totals all the time in your first post, I assumed you must have one. I can't see how you manage without it.

    I have created a payment table (with CustomerID) to record the received payments
    So when you get a payment, do you not keep track of which invoice or order the payment was for? How can you tell which orders have been paid or not?

    But I create invoices from "Orders" and "Order Details" tables using QUERIES, I do not have "Invoice" table. How can I use InvoiceID without "Invoice" table?
    OK, but if you or a customer need to know which invoice was for a particular order, how can you answer the question if you don't keep track of the invoices?


    but I do not have an idea of how to relate the payments with invoices
    You can't, unless you have an invoices table. For proper tracking, you need an invoices table, because

    a) one order can have several invoices associated with it (products back ordered etc.)
    b) a customer payment could be for several invoices, or only a partial payment on one invoice
    c) one invoice might cover several orders (if your business procedures allow for it)

  5. #5
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    Ok, I see. Instead of Invoice table, I have BILLS table and BillsPayment table. In BILLS table, I have these fields:
    Bill_ID, BillNo, BillDate, BillDetails, BillAmount.
    Currently I enter BillAmount manually, but now I want to automate the BillAmount. I can get the total Receivable amount of a customer using query but I can not put that value to BillAmount in BILLS table. Need help

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    OK, you use "Bills" - I'll use that.

    In your Bills table, you have these fields:

    Bill_ID, BillNo, BillDate, BillDetails, BillAmount.

    But if those are the only fields in there, than you have no way of relating the bills to the customers, orders, or anything else. You have to have at least the CustomerID in there, otherwise you will have all kinds of problems tracking which orders have been billed and/or paid.

    I can get the total Receivable amount of a customer using query
    Can you give some more details as to what data the query will retrieve? There a number of things to consider. You have the Customer ID, no problem. But if the customer has several orders that have not been billed, but only some of those orders have been filled? How do you identify orders that have been completed and billed, but not paid yet? Etc., etc.

    Do you you see where I'm going here? There are a lot of situations that you have to take into consideration.

    See my next post for another question.

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Some businesses use a different method of working with customer accounts. When customers make payments, they often do not indicate exactly which invoice(s) the payment is for; it could cover multiple invoices or it could just be a "payment on account"

    Businesses avoid difficulties like that by keeping an account (ledger) for each customer, and when a bill goes out, they debit the account, and when a customer makes a payment they credit the account, without detailing what the payment was for (i.e. which invoice). In that system, each customer carries an account balance to show what they owe at any point in time.

    Is that system similar to the one you are or will be using? It affects what tables you have and what data items you keep in them.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-15-2017, 02:36 AM
  2. Replies: 12
    Last Post: 07-18-2014, 01:22 PM
  3. =Sum with a max amount
    By spacekowboy in forum Reports
    Replies: 4
    Last Post: 05-15-2014, 07:32 AM
  4. Replies: 3
    Last Post: 03-20-2013, 01:31 AM
  5. Value of latest date
    By v!ctor in forum Queries
    Replies: 3
    Last Post: 02-27-2013, 03:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums