Need Help Obtaining The Running Total Of A Count And Grouping It By Month

[ATTHECROSS]

Hello All -

Here is my SQL for this query:

/SELECT [Q_OH-IFC_CO].ACTUAL_FINISH, Q_FiRM_PROJECT_DATA.POLE_COUNT_PER_SECTION
FROM Q_FiRM_PROJECT_DATA LEFT JOIN [Q_OH-IFC_CO] ON Q_FiRM_PROJECT_DATA.PROJECT_ID = [Q_OH-IFC_CO].PROJECT_ID
WHERE ((([Q_OH-IFC_CO].ACTUAL_FINISH)>#1/1/2018#) AND ((Q_FiRM_PROJECT_DATA.PROJECT_TYPE)="OH") AND ((Q_FiRM_PROJECT_DATA.JOB_TYPE)="Reconductor" Or (Q_FiRM_PROJECT_DATA.JOB_TYPE)="Long Span"))
ORDER BY [Q_OH-IFC_CO].ACTUAL_FINISH;/


FYI:
- The count is in whole numbers
- the finish dates have a format of 1/1/2018

What I am trying to do is get a simple cumulative (running) total of the (pole) count that is grouped by month. I don't need the year in there since the cumulative total starts in January and runs to december 31st and then I will move the ">" date to the next year. I am trying to get this data to be my source data for an excel line chart.



I have tried for hours to figure this out. I have tried using Dsum to get the cumulative total but then can't figure out how to break it out by month. I am not sure what I am missing.

Thanks for the help.

[June7]

Have you considered the RunningSum property of textbox on report?

[ATTHECROSS]

I haven't because I generally produce excel output from my access data rather than working with access reports. So I am not real familiar with access reports.

[CJ_London]

think you need to go back a step and also provide the sql to Q_FiRM_PROJECT_DATA. At the moment I don't see any fields you can use to provide what you want. Would also help to see some example raw data and the required outcome to ensure we are not talking at cross purposes.

[June7]

https://support.microsoft.com/en-us/help/290136/how-to-create-a-running-totals-query-in-microsoft-access

Ajax makes a good point. Might have to build another query with DSum() that uses posted query as source.

Expect this type of query to perform very slowly with large dataset.

Also advise you learn about Access reports. I once inherited a db that used Excel as user interface for data entry and reporting. Quickly learned how to use Access forms and reports to accomplish the same and totally eliminated Excel.