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*(monthly 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).

[NeoPa]

So, working to the following data (you didn't give details of your Rate table so I made one up myself) :
Table = [tblRate]

Field      Type       Index
Month      Date/Time  PK
Rate       Currency

The Month value would be the Primary Key and would be the date of the first day of the month. The value returned for February from CDate("February 2010") in fact.
Table = [ACCVehicles]

Field           Type       Index
ownerID         Number     FK
Reg             String     PK
DateAcquired    Date/Time
DateDischarged  Date/Time

There would need to be a form ([frmInvoice]) with a control ([txtMonth]) containing the Date value of the month the invoice is required for.

The query (upon which the report could be built) would be something along the lines of :

SELECT tAV.Reg,
       IIf(tAV.DateAcquired<CDate(Forms!frmInvoice.txtMonth),
           CDate(Forms!frmInvoice.txtMonth),
           tAV.DateAcquired) AS StartDate,
       IIf(tAV.DateDischarged>=DateAdd('m',1,CDate(Forms!frmInvoice.txtMonth)),
           DateAdd('m',1,CDate(Forms!frmInvoice.txtMonth))-1,
           tAV.DateDischarged) AS EndDate,
       tR.Rate*
           IIf(DateDiff('d',StartDate,Enddate)>28,28,
               DateDiff('d',StartDate,Enddate))/
           28 AS MgmtFee
 
FROM   [ACCVehicles] AS tAV,
       [tblRate] AS tR
 
WHERE  ((tAV.DateAcquired<DateAdd('m',1,CDate(Forms!frmInvoice.txtMonth)))
  AND   (tAV.DateDischarged>=CDate(Forms!frmInvoice.txtMonth))
  AND   (tR.Month=CDate(Forms!frmInvoice.txtMonth)))

NB. This produces a Cartesian Product set of data until the WHERE clause kicks in. It should nevertheless produce the correct results (assuming I've got it right).

Clearly, you can choose your own names for the objects, but the code would need to reflect these changes if you choose to.

[tasawer]

Excellent solution NeoPa especially when my information was breif. I have a better understanding of it now.

let me give more details now that I understand it better:

Below are the fields used in the table "VehicleManagement" and form "mgmtInvoice"

ManagedID Autonumber
VMRef (invoice Number)
ManagedCoID (VehicleOwner) (Same as VehicleOwnerID in ACC_Vehicles)
mgmtMonth (Month for Invoice)
mgmtFEE
 

on the form "mgmtInvoice"

• I create a new invoice and VMREF is inserted using DMAX

• Month for invoicing is automatically inserted in mgmtMonth

• mgmtFEE is picked up from a table of Rates

• using a combox, I select my customer ManagedCoID

at this point, in a subform, I want to list all the vehicles for this customer and assign the mgmtFEE to each vehicle.

we manage vehicles for long period of time therefore we need to invoice for every month of management. (Your current solution lists only discharged vehicles)
if a vehicle comes in on January 24th 2008, and discharged December 20th 2009, we will charge 7 days for January and 20 days for december but full mgmtFEE for all other months. Thereafter this vehicle must not be listed in any of the invoices.

query will be named mgmtDetails

I hope this is clearer.

[NeoPa]

I don't really want to start rethinking all this again from scratch, but I realise now that it does indeed ignore items where there is no discharged date. That concept wasn't really introduced, to be fair, but had I thought about it more deeply I would have realised it must be so.

What I propose is that I give an example of what is required using the scenario outlined before, and you can implement that within your own scenario. Should you then encounter difficulties then come back and explain what you are struggling with, and I'll do what I can to help.

SELECT tAV.Reg,
       IIf(tAV.DateAcquired<CDate(Forms!frmInvoice.txtMonth),
           CDate(Forms!frmInvoice.txtMonth),
           tAV.DateAcquired) AS StartDate,
       IIf((tAV.DateDischarged Is Null)
        OR (tAV.DateDischarged>=DateAdd('m',1,CDate(Forms!frmInvoice.txtMonth))),
           DateAdd('m',1,CDate(Forms!frmInvoice.txtMonth))-1,
           tAV.DateDischarged) AS EndDate,
       tR.Rate*
           IIf(DateDiff('d',StartDate,Enddate)>28,28,
               DateDiff('d',StartDate,Enddate))/
           28 AS MgmtFee
 
FROM   [ACCVehicles] AS tAV,
       [tblRate] AS tR
 
WHERE  ((tAV.DateAcquired<DateAdd('m',1,CDate(Forms!frmInvoice.txtMonth)))
  AND   ((tAV.DateDischarged>=CDate(Forms!frmInvoice.txtMonth))
   OR    (tAV.DateDischarged Is Null))
  AND   (tR.Month=CDate(Forms!frmInvoice.txtMonth)))