Setting up a Paid in Advance

[bugman]

I have a problem with figuring out how to implement what I call “Paid In Advance”. If a customer pays 4, 5, 6, or 12 months in advance, I for some reason can’t think of how to set this up with tables, queries, forms, and reports. The example that closely matches my db is “Order Entry” from access sample db’s.

Tables are as follows:
tblCustomerDetail
tblCustomerHistory
tblPayments

For every customer (tblCustomerDetail) there are multiple service records (tblCustomerHistory) and for every service record there are or can be multiple payments (tblPayments) for that one service record.

Just like in “Order Entry” where Customers have multiple Orders and then one or many Payments for that one Order. (Pretend that Order Details is in the Orders table.)

The problem that I’m having is trying to come up with a way to assign the “Paid In Advance” money to the customers account without assigning it to a service record or payment. I can’t assign it to a service record because it hasn’t been serviced yet. And I can’t assign it to a payment because the service record generates a new record in the tblPayments when a new service record is entered, and besides that wouldn’t work because the “Paid In Advance” money would only be applied to that one service record.

I hope everyone is following what I’m saying.
If anyone has any clue as to what I am trying to do, please feel free to help me.
As always any help would be greatly appreciated.

Thanks

P.S. If anyone knows where there is a sample db of what I am trying to do, I would be grateful if you could post the link in your reply.

 

[Vassago]

How about a balances table that would store any paid in advance moneys or moneys owed. Then when a service is requested, it can be deducted from a balance table and applied to your payment table.

 

[bugman]

First of all thanks for responding. I have looked it over and it seems to me to be a little bit on the complex side of things. The following is the way that I see what you are saying.

First, build a table tblCredit then make a relationship with tblCustomerDetail. Next, build a Form frmCustomerDetail then have subfrmCustomerCredit attached to it. Now when a customer is worked and they decide to pay extra, like a year in advance, then I would open the form “frmCustomerDetail put the extra moneys in the subfrmCustomerCredit for that account.

When I’m doing the entries for the service records worked, I do the following:

Open frmCustomerEntry attached is subfrmCustomerHistory.
Then pull up the account that was worked and on the subfrmCustomerHistory enter in the service record. Ex: Time In, Time Out, Area Serviced, Service Date and Amt of service charge. At the end of the service record it will open a form frmCustomerPayments. At that time I will input what type of payment, i.e. Check, MoneyOrder, Visa and so on and the Paid Amt and Paid Date.

The only thing I can think of is to have a procedure that when I get to the end of the service record on the subfrmCustomerHistory and it goes to open the form frmCustomerPayments, it will check that account and see if it has a credit or not and if it does it will give you the option to open the frmCustomerDetail and subtract the moneys from the subfrmCustomerCredit.

Please let me know if this is what you are talking about, if I need to change something or do it a totally different way. Also, if anyone else has any other ideas it would be greatly appreciated.
Thanks Again.

 

[dheich4321]

I have a database that is a "recievables" database. The user enters invoices and payments. It is made up of a customer table, an invoice table and payment table. All are linked by the customer no. Queries determine the balances and totals of invoices and payments and reports calculate the net credit or debit on the account. Seems to work.

 

[dheich4321]

One more thought. The payment table requires an invoice to be entered, but does not validate it to the invoice table. The user enters a "dummy" invoice number when a customer is paying in advance. They then go in and change the invoice number on the payment after the invoice is received.