Summarizing data and reporting running totals by month

[bcooler]

Hello!

I need help summarizing data for a report. The report needs to show a running 12 month history of the number of "findings" opened in a given month. So, in Excel-world, I would have used a "countif" function on every month column.

I have tried to use pivot tables and crosstab queries, but they need a row heading. If I use the "Findings_ID" primary key as the row heading, I get every record since they are unique.

I know this must be very easy, but I'm just not quite sure. Does "DSum" enter the picture here?

Thanks!
Brian

 

[dhookom]

Please share significant information about your tables, fields, and sample data. The final output would also help us provide targeted assistance.

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Ok, no problem. I have 1 table named tblData and one field named Finding_Date. With that one field, I would like to create a report that shows # of records by month for the last 12 months. If it helps, the primary key is called Finding_ID.

Does that help?

Thank you for taking the time to help me.
Brian

 

[dhookom]

So tblData has only two fields Finding_Date and Finding_ID?

How about providing
[ul]
[li]all of the significant fields[/li]
[li]sample raw data[/li]
[li]sample results in report[/li]
[/ul]



Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Alright, I got somewhere by adding a priority as the row header (turns out I needed that anyway). This code seems to do about what I wanted.

TRANSFORM Count(tblData.Finding_Date) AS CountOfFinding_Date
SELECT tblData.Priority
FROM tblData
WHERE (((DateValue(Month([tblData]![Finding_Date]) & ", " & Year([tblData]![Finding_Date])))>DateAdd("m",-11,Now())))
GROUP BY tblData.Priority
PIVOT MonthName(Month([tblData]![Finding_Date]),True) In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

This is just the SQL that came out of my query builder. It gives a running 12 month report of the total number of instances. I'll let you know if this causes unforseen issues later.

Thanks!