Counting open events by month (1 Viewer)

[Bat17]

I have a table, part of which includes a [start date] and [end date]. I need to be able to plot a graph that will show, for each month, how many projects were still open. they will at the start of the month have a [start date] but no [end date]. I have created another table that holds the date for the first of every month in the range that I am intrested in. I suspect I need to use a subquery in here somewehre but cant get my brain around it

Many thanks

Peter

 

[Ron_dK]

I would make a query something like this :

SELECT Yourtable.startdate, Yourtable.enddate, (Date()-[enddate]) AS curr
FROM Yourtable
WHERE ((((Date()-[enddate]))<0));

This would give you all projects still running instantly.

Hth

 

[Bat17]

That would be fine if I just want current data, but, I need to do a graph showing the number of events that were open at the start of each month over the last 12 months so that we can see the trend
I could do it by calculating it monthly and storing the data but that goes against the grain

Peter