How to deal with irregular payments

[Athar Khan]

I am a trucker/transporter and I transport the goods of my customers. I am paid per trip.

Case 1:
One of my Customers pays me 100% freight on the spot. I have a 'ReceivedFreight' field in my 'OrderDetails' table. I subtract the received freight and 'Balance' becomes zero for this record.

Case 2 (A) :
A Customer give me partial payment (as an advance), 'Balance' = remaining amount (for this record)

These types of customers pay the remaining amount after a few days (not fixed). Receiving of the freight has no regular routine.

Case 2 (B) :
Sometimes a customer pays me the remaining balance amount for a single trip (1 record).

CASE 3:
Sometimes a Customer pays a lump sum amount (for example, $2000) for multiple trips without knowing or caring the exact figures of the Invoice. He says that we will adjust it later.
In this case I can not enter the received amount in 'ReceivedFreight' because it's a big amount and not specifically paid for an Order or Orders (invoice)



I think I have to open an account for each customer to deal with these kinds of irregular payments but at the same time I will have to maintain the advance payments received against some orders in the invoice.

But I don't have an idea how to design and relate the required table(s) to deal with this situation.

[Bulzie]

Not sure how simple you want to make it. If just for you to keep track of things, could do a single record and just add fields for say PartialPayment1, Reason1, PartialPayment2, Reason2, and just have them all calculate your balance, etc.

Or normal way something like:
tblInvoice - InvoiceID(auto),CustomerID other fields about the specific invoice
tblCustomer - CustomerID, other fields
tblPayments - PaymentID(auto), InvoiceID, TypeofPayment, PayAmount, PayDate, etc. (this table could have multiple records for an invoice if various payments made)

[rpeare]

I think bulzie's solution is a good one, you could get a bit fancier and use some code to help split up receipts to outstanding bills applying it to the oldest unpaid bill first.

[Athar Khan]

Thanks Bulzie and rpeare. I will try it