Cumulative Question

[03Explorer]

I need to generate a report that counts incidents per month. But the catch is from month to month (months sorted ascending) need to include previous months totals). example chart below

Aug 03 =1
Sep 03 =2
Oct 03 =2
Nov 03 =3
Dec 03 =4
Jan 04 =8
Feb 04 =8
...

I am using one field, a date field. I am including my SQL code that I am trying to figure this out with.

SELECT 
  Format([cons_date],"yy") AS sort_year, 
  Format([cons_date],"mm") AS sort_month, 
  Format([cons_date],"mmm") & " " & Format([cons_date],"yy") AS Consent_date

FROM 
  DBA_mem_us_baseline AS m

ORDER BY 
  Format([cons_date],"yy"), 
  Format([cons_date],"mm");

I extracted the year and month for sorting purposes.

Thanks

 

[dhookom]

You should be able to simply set the Running Sum property of your text box to Over All.
Your report's record source query would be:

SELECT Year([Cons_Date]) as Yr, Month([Cons_Date]) as Mth, Count(*) as NumOf
FROM DBA_mem_us_baseline
GROUP BY Year([Cons_Date]), Month([Cons_Date]);

I don't generally do any sorting or formatting in the record source query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]