Create series of dates

[Tskutnik]

Thanks in advance for any help.
I need to create a report with projected earnings over a series of month end dates. The source table looks like this

JobName | MonthlyAmount | StartDate | EndDate
Job1 | $500 | 1/31/15 | 11/30/15
Job2 | $750 | 3/10/15 | 9/30/15
Job3 | $600 | 2/20/15 | 1/31/16

The user selects the data rage to be run
e.g. 9/30/15 - 12/31/15

The output should look like this
9/30/15 | 10/31/15 | 11/31/15 | 12/31/15
$1,850 | $1,100 | $1,100 | $600

I'm not sure how to turn a date range (StartDate, EndDate) in the table into multiple records in the output.

Thanks again

 

[Ranman256]

create a report form, on it, put 2 text boxes:
txtStartDate and txtEndDate

all queries will look at these boxes to limit the data..

select * from table where date between forms!frmRpts!txtStartDate and forms!frmRpts!txtEndDate

you can put a combo box to pick a report, a button will open this report or query

 

[Tskutnik]

Thanks. I think my question was more about the underlying query and how to segregate and add the data by month. Your post is on the text boxes that will act as the filters, which I also needed, so thanks. If I should move this to the Query section of the forum please let me know.

 

[The_Doc_Man]

To create a report, you generally need to have the required data already extant in a table. Oh, you can use a query to do the extract, all right - but you probably needed to have created the entries with some sort of predictor code to add the data for your projections. Once you have it in a table, getting it out is almost nothing.

I suppose it WOULD be possible to define a query to make some sort of projections without there being any underlying table data past the first record or two, but the trick in such cases is to get it to stop creating "virtual" records so that your report actually has an ending period.

I think building a table first - even a temporary table - would be the easier way to approach this problem. (At least, I think that is true if I understand what you wanted.)

 

[Tskutnik]

I'm going to close this thread and move it to the Query section. I did not describe the problem very well. Thanks for the help.

 

[pbaldy]

The follow up thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=290717

I'll close this one.