Count months then put in year columns

[Blkblts]

I appologize if this has been posted before I have been searching all afternoon off and on and have not found anything yet. Using Access 2002 SP3 on an Windows XP

.

I have a table that contains the following

name
effective date
renewal date
monthly premium

I need to calculate the following. If effective date is 10/01/2004 then multiply monthly premium by 3 for a total for 2004. Then if renewal date is 10/01/2005 I need to mulitply the monthly premium by 9 for 2005 total. I have dates that go from 2004 - 2007 I need to be able to calculate total for year by name irregardless of todays date.


Sample expectation (based on above example.)

Name 2004 2005 2006 2007
rr $12,000 48,000 0 0

Thank so much in advance for anyones help.

 

[gromit]

hi -

Not sure if I get all the ins and outs of your calculations, but here goes:

You can use MONTH to figure out the number of months for a date. E.g. MONTH(#10/01/2004#) = 10 (for US date conventions).

So
12 - MONTH([EffectiveDate]) + 1 = 3 <---- gets you your 3
MONTH([RenewalDate]) - 1 = 9 <---- gets you your 9 months

You will also need to check the year for the calculations.

See if that helps, if you still have questions, post back.

- g

 

[Blkblts]

Thank you yes that gets me further on my calculations. I appreciate your reply. I will trudge on with rest.

K