Create Recurring Invoice

[tasawer]

Hi,

I am looking for guidelines to create a monthly recurring invoice using the following criteria.

We manage vehicles for Rental companies and therefore we require to invoice same amount each month. (monthly fee is based on 28-day month)

Invoice report must show vehicle reg, date vehicle acquired (or discharged) and management fee for each vehicle,

at the moment we have a set number of vehicles to manage but in future, a new vehicle may be acquired mid-month or it may be discharged mid-month and therefore payment will be calculated on the number of days managed (noofdays*(mont hly fee/28))

table ACCVehicles - holds ownerID, Reg, DateAcquired, DateDischarged and data of managed vehicles

where do I go from Here?

As always, your help is appreciated and thanking you in advance.

[Yene]

You have everything you need to create an invoice table. but I think you might also need a table for your customers and the cars you manage. After creating the 3 tables all you have to do is link the tables. are you using MS access or SQL for ur Database?

[NeoPa]

What is the rate for these vehicles? Does it vary per vehicle, or is it global?
Do you store anywhere what has already been charged and, separately, what has been paid?

It really is most important that the data structure is sorted out first before any further design is done. Designing to the wrong structure is a very slow way to progress, and quite disheartening, as you have to keep throwing away and starting again.

[tasawer]

Hi,

the rate for vehciles is global.
it is based on 28-day month
if we hold a vehicle for 10-days we charge (Rate/28)*10
if we hold for more then 28 days per month, the exra days are for free.

one method in mind is to create one Invoice with details of all vehicles per client and to copy this invoice every subsequent month.
If a vehicle is added or discharged, this can be dealt individually.
Fear is that all vehicles may not get included especially when one client hold 100+ vehicles.

The method in mind was to
1. Open Invoice Form
2. Select Client - and display all vehicles for this client
3. Create Invoice - All vehciles would be added to InvoiceDetail and charge rate calculated automatically.

[NeoPa]

@tasawer
We figured you wanted to use Access rather than Excel.

I cannot recommend this approach for the sorts of figures you seem to be dealing with. A database approach would be far more suitable. That would include storing the relevant data in appropriate tables and producing an invoice in a Report which (obviously) would be designed around a query.

If you're interested in proceeding with a database approach I'm sure we can help you, assuming you can pass us the relevant information. Where, for instance, do you hold the value for the rate charged (not the calculation)?

[tasawer]

I prefer the database method as this is an addition to my main database project that you have already seen.

I can certainly pass the database or other relevant details to you.

For the management rate, I could create a table with datestart and dateend fields. During invoiceing, rate can be picked up according to the date.
(Similar to what I did with the VAT rate)

I have a vehicle Managements table that is used to record each vehicle acitivty with VMREF as the invoice invoice number.
I prefer to use this table to hold the header info for the Management and use VMREF as the invoice number.

how would like the information.

Regards

[Yene]

It seems to me you have every thing in order, unless you want some help in putting the database together. i.e one car can have only one client owner, but a clinet can have more than one car. one car hold one slot in a holding plot. but holding plot can have many car and so on......

[NeoPa]

I can't understand your answer tasawer. I thought it was a simple question. Where is the rate stored?

Your answer seems to introduce the idea that it is variable by date. This contradicts what has already been stated. I'm stuck on this until I get an answer that is consistent with your explanation so far - or some sort of explanation why it might not be.

[tasawer]

sorry about the the confusion NEOPA.

we have been using the same rate in all our invoices for the last coupleof years, but planning ahead, I believe it will be better for the rate to be variable by date period.

[NeoPa]

It's generally a good idea to keep the question still until a satisfactory answer has been received at least. I expect you can understand quite easily why this would be. Once an answer is working then variation may be introduced without danger of confusing everything too much.

Let me look at your updated question though, and see what I can come up with. It is clear now at least (as far as I can see now at least).