Running Ballance

[RobertBC]

Good Day!

Im Trying To compute customer running balance, i create a query that will view all Invoice by customer and the amount(Filed: InvoiceID, InvoiceDate, CustomerID, Amount) and query Payment (Field: CustomerID, PaymentID, PaymentDate, AmountPaid)

then the total sum of all invoice by customer is computed and also the total Payment by customer. after that I subtract Total Customer Payments into Customer Total Invoice to get the running balance..

question: how do i automatically compute or create a procedure for records and add additonal field into the invoice indicating that invoice is (Paid, Partially Paid, Over Due and Due). for further report option that i can view all paid or overdue or even due invoices.

thanks!

[matthewspatrick]

Textbox controls have a running total property that you can set to tally a running sum. You might also consider a slightly different schema:

tblCustomers
-----------------------------------------------------------
CustomerID (PK)
<customer attributes>

tblInvoices
-----------------------------------------------------------
InvoiceID
InvoiceDate
CustomerID (FK)

tblInvoiceDetails
-----------------------------------------------------------
InvDetailID (PK)
InvoiceID (FK)
ProductID (FK)
Qty
DiscountApplied
ShippingCost
TaxApplied

tblProducts
-----------------------------------------------------------
ProductID (PK)
ProductDescr
<other product attributes>

tblProdPrice
-----------------------------------------------------------
ProductID (PK, FK)
AsOfDate (PK)
UnitPrice

tblPayments
-----------------------------------------------------------
PaymentID (PK)
InvoiceID (FK)
PaymentAmount

[RobertBC]

Good Day!

i attached a sample database with minimal data on it.. can you pls help me creating a report taht will compute customer running ballance base on computed total invoice by customer subtracted to total Payment by customer... to get teh exact runing ballance..

after that i want on the invoice to get another field or maybe crating a new table to store if the invoice is paid already or give a status of (Paid, Partially Paid, Over Due, and Due)

i wnat to use those invoice status for my further report by status like all overdue invoices or maybe all paid invoices..

thank you very much! :friends: :bow:

[OBP]

In the report do you want just the running balance or to list the items as well?

[OBP]

This one has the report with running totals. It is based on the Customer query.

[RobertBC]

In the report do you want just the running balance or to list the items as well?

Thanks sir for the example....

my problem here is i have query that list all the invoice by customer and sum its Total Amount to get all the ballance by customer.. as well i have query to list all the payments made by customer and compute its total Amount.. then i subtract the Total Amount from Invoice to total amount for payment to get the existing ballance of a customer...

what will i need to get is how do i add another field to invoice table or maybe create a new table if necessary to give status to invoice if its (Paid, Partially Paid, Over Due and Due)

http://i63.photobucket.com/albums/h134/cool_as_ice/runningballanceflow.jpg

[OBP]

Robert, perhaps you had better post your latest version of the database on here, so that we can see what you are working with now.

[RobertBC]

good day!

i attached my sample dba with minimal data on it.
open the CustomerRunningBallance report to list the invoice and payment history of the customer..

notice that on the invoicemaster table i had a field "Status" on this field
i want to put a invoice status (Paid, Partially Paid, Over Due and Due)

how do i automatically insert a value on that field base on the payment history of the customer...

sample flow:

InvoiceID 1: amount is 5,000.00
InvoiceID 2: amount is 3,000.00
Total Invoice is 8,000.00

PaymentID 1: 4,000.00
PaymentID 2: 1,500.00
PaymentID 3: 500.00
Total Payment is 6,000.00

on status for InvoiceMaster table
InvoiceID 1: 5,000.00 less Total Payment wich is 6,000.00
remainder is -1,000.00

for the status of my InvoiceID 1: is "Paid"

the remaining 1,000.00 now will deduct to remaing invoices
invoiceID 2: 3,000.00 less the remaing Payment Total Amount (1,000.00)

for the status of my InvoiceID 2: is "Partially Paid"

and so on on another invoices.....

:friends:

thanks

[OBP]

Robert, you have posted the RAR file not the Access MDB file.

[RobertBC]

yes i use winrar and then zip it again.. the file is too big that exist forum limit for attachement but if you extract it you will get my sample mdb file..

thanks

[OBP]

Robert, you need to "Compact & Repair" your database before zipping and posting. It is now 265Kb after compacting.
I have noticed that this version has "lost" it's forms, which is where the VBA code needs to go to do this calculation.

[RobertBC]

here are my newly created database it compact now..

thanks for your time.....