I have a table where the user will enter a commencement and ending date and an amount that was charged for the period of time. This is all on a subform, link together by an account number. Also, each record on the subform, may have multiple dates For example:
User may enter 02-21-98 to 04-30-02 $5.00
05-01-02 to 02-28-05 $10.00
03-01-05 to 05-15-06 $20.00
I want a query that will take each record of dates and list all the Dec 31st of each year between the two dates or up to the end month of the period for that record. So basically I would have
The query should give me
Including Month Rate Accrued
12-31-98 $5.00 $50.00 ( $5.00 x 10 months)
12-31-99 $5.00 $60.00 ( $5.00 x 12 months)
12-31-00 $5.00 and so on......
12-31-01 $5.00
04-30-02 $5.00
12-31-02 $10.00
12-31-03 $10.00
12-31-04 $10.00
02-28-05 $10.00
12-31-05 $20.00
05-15-06 $20.00
( because its the last date)
Any ideas...
Thanks!