Forum home » Delegate support and help forum » Microsoft Access Training and help » Pro-rata Calculation - Not for the Faint Hearted!
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Pro-rata Calculation - Not for the Faint Hearted!
Resolved · Urgent Priority · Version 2003
Reply Sun 30 Aug 2009, 20:07Delegate Scott said...
Scott has attended:
Excel VBA Intro Intermediate course
Pro-rata Calculation - Not for the Faint Hearted!
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
For upcoming training course dates see: Pricing & availability
RE: Pro-rata Calculation - Not for the Faint Hearted!
Hi Scott
Thanks for your post and after reviewing it is something that is beyond the scope of this forum to address.
If you would like us to have a further look into this we would ask you to send through examples of your working files.
We will then be able to let you know of any proposed solutions and related costs to achieve this (trainer development time etc.).
Please let us know if you would like to proceed.
Regards
Jacob
Reply Wed 2 Sep 2009, 16:56Delegate Scott said...
RE: Pro-rata Calculation - Not for the Faint Hearted!
Thanks Jacob,
Actually - I've used an alternative forum while I waited for you to reply and they suggested using a function, which I did, succesffuly.
Best,
Scott
|