create specific months between start date and end date

M

momo_brad

I have start dates and end dates for licences over a period which they are
expected to generate an average monthly revenue (calclulated from other
tables). I used a datediff function to get the number of "active" months per
licence but need to somehow create a record for each licence for each month
it is active so I can apply the revenue value monthly and be able to display
monthly totals and yearly totals in a report. any suggestions?

thanks
 
T

Tom van Stiphout

On Thu, 27 Nov 2008 11:54:01 -0800, momo_brad

The classic solution is to have a table with all months in it, and
outer-join to it. That way you will have all months, and whatever
payments may have happened.

-Tom.
Microsoft Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Apply Month End date based on transaction date 5
Project Start and End Date is not consistent 1
date calculation in pivot table to show products over agreed date? 0
Creating report with date counts 1
SQL to determine Start Date and End Date for consecutive dates 1
Help showing active months between two dates 5
Dates & Future Dates 3
Compare current month totals with last months totals 1

Top