I have pre christmas developers block and cant figure this out, it appears simple!!

I have a table with Date Started Treatment, Date Treatment Completed going back 3 years.

I need to produce a monthly summary for the last 12 months to show how many people were in treatment at the end of each month, also how long they were in treament for.

I would like to do it in one query which I can then pivot to make a graph from.

Your going to need to generate the end of the month dates with something like this :
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=29069&d=1254389912

I made a while back.

Then have your query "full outer join" (or not join) with this but limit it to return only those end of months between the dates. then its a simple matter of counting.

Cheers - the crux of my problem is that I need to count those in treatment at the end of each month, but they might have started before that month so they dont have any date values in that month.

Which is why you need to fake the months using something simular to the file I attached.