Pro-rata Calculation - Not for the Faint Hearted! | Access forum

I've been struggling with this for a week before finally deciding I need help - big time!

I need to run a query to create a charges table, using the following to calcualte a current charge amount:

StartDate
BilledUpToDate
EndDate
UnitCost
Units
InvoiceDate

So in English this would work out something like this:

If the start date is less than the invoice date AND the EndDate is less than the BilledUpToDate, THEN Units * UnitCost, Pro-rated from:

(Either the StartDate or BilledUpToDate, whichever is most recent) to either the EndDate or the InvoiceDate (default if EndDate is NULL) whichever is first in time).

Anyone have any ideas as to how I'd even begin to go about achieving this?

I could and have done this in VBA for excel but getting frustrated without much knowledge of SQL / VBA in access for this task. It's really the method rather than the calculation I need help with. Below is some example data for testing examples with correct results.


ChargeID StartDate ChargedUpTo EndDate InvoiceDate Units UnitCost Correct Result
1 01/07/2009 31/07/2009 31/08/2009 31/08/2009 1 10 £10.00
2 16/07/2009 31/09/2009 31/08/2009 1 20 £30.32
3 30/06/2009 31/07/2009 31/08/2009 2 30 £60.00
4 15/08/2009 15/08/2009 31/08/2009 10 10 £54.84
5 20/07/2009 31/07/2009 15/08/2009 31/08/2009 10 20 £232.67
6 01/01/2009 31/07/2009 31/08/2009 1 30 £30.00
7 20/05/2009 15/03/2010 31/08/2009 1 40 £100.65
8 15/04/2009 31/07/2009 31/08/2009 1 10 £10.00
9 06/06/2009 31/08/2009 1 20 £36.13
10 15/05/2009 31/07/2009 31/08/2009 1 30 £30.00