Auto Invoicing

[keiath]

Hi

I am creating a Database for a letting agent, on the 1st of every month I want access to automatically Generate a rent demand, and I will have a payment screen, where I enter when the tenant pays there rent

What I am looking to do is this' There is a Tenant screen' with details of the tenant, the property there are renting, the amount they pay,

From there I want a subform showing payment history that would look something like this:-

Date Rent Demanded Rent Paid Balance
01/07/18 £550.00 £ 550.00
02/07/18 £550.00 £ 0.00
01/08/18 £550.00 £ 550.00
01/09/18 £550.00 £1100.00


09/09/18 £400.00 £ 600.00

So its a complete history of demand and payments, that can displays the outstanding balance.

Thanks in advance

Keith

[pbaldy]

I would not try to store balance, I'd store transactions, invoices as positives and payments as negatives. It's easy to calculate the balance at any given point in time from transactions.

As to automatically invoicing, I'd probably have an app that ran monthly from Windows Scheduled Tasks that created invoice transactions.

[keiath]

This is currently how I am doing it not sure I like it that much as I seem to be having a problem putting both the Demand Table and the Payment Table into one query - not sure why? just flats v.1.1.zip

I have attached a zipped file of the database

THanks Keith

[pbaldy]

With separate tables, you could use a UNION query to pull them together and query against that. Or you could sum them separately and then put them together. I'd probably go with a single table. Other thoughts from a brief look:

• The payment and invoice tables should only include the tenant ID, just as they include the property ID. You shouldn't have name fields in those.
• Spaces and symbols in object names are not worth the trouble in the long run.