I have a need to create a database of orders for "ads" that run over a particular date range. Each of those ranges has a $$$ amount associated with it. For example:



Start: 3/1/10, End: 3/31/10, Cost: $400
Start: 3/15/10, End: 3/22/10, Cost $100

There will be hundreds of these records, some running for 1 day, some for months. I want to create a report "by week" of income. Entering records for each day would be too time consuming.

I had created an AdDetail Table which had StartDate, EndDate, and Cost, and was considering creating an AdDetailDaily Table, and "manually creating" daily records from each of the "detail records" where I'd divide the cost by the #days in the range. But that seems "wrong".

Any ideas on the "right" way to do this. I could generate an effective report from the AdDetailDaily table, but I'd continually have to drop all data and regenerate it. Is there a way to do what I need in a query (that I could base a report on)?

TIA for an tips/advice!

Steve