Running total calculation for Month to Date value for every day in the month

[KyleB]

I've been unable to find anything in the archives about this so far, but what I'm attempting to do is create a query based on tables with three columns; for the express purpose of creating a graph on a report.

The first field is the day/month/year.
Second field is forecast month to date.
This much of the data is of the form:

Date ForecastbyDay
3/1/04 100
3/2/04 200
3/3/04 300 etc...​

Third Field is a cumulative Actual Data Month to Date.
The full data set would look like this:

Date ForecastbyDay CumulActualsbyDay
3/1/04 100 245
3/2/04 200 367
3/3/04 300 418
3/4/04 400 596 etc...​

So the forecast data by day is merely a calculation that takes the total forecast number for the month (3100) divided by the number of days. This value I can obtain without difficulty. The Actual data set is entered and I can tabulate this information by day. What I can not figure out how to do is take the actual daily values of: 245, 122, 51, 178 and create the cumulative total of 245, 367, 418, 596 etc on a daily running total.

Ultimately I wish to create a line graph out of the forecast data and a Bar graph out of the actuals, both on the same plot so that we can track actuals vs forecast on a daily basis on this graph.

Field Names and Values changed to Protect the Guilty.

Kyle

 

[Cosmos75]

See if this sample database helps you out.

 

[KyleB]

Ok this solved the problem. I'd looked at several of those posts but the detail was more than I could decipher, and from what I could tell they weren't trying to do what I wanted. After re-reading them, I found a reference in one of the supplemental posts that did what I wanted. I unfortunately, am unable to completely decode this gobbledy-gook, though I mostly understand it.

RunningSum: (Select Sum([Actual Ore Tons]) from [qry2 Daily Collected Data Calculations] as a where a.[Month/Day/Year]<=[qry2 Daily Collected Data Calculations].[Month/Day/Year])

As I understand it I am selecting the summed value for the day, from my base query, where the date is an increasing value. I don't really understand what the "as a where a.[Month/Day/Year]" means in the code though. I haven't seen that syntax before. But simply put, this does do what I wanted and for each day of the month it sums the current day's total with the Month-to-Date total for the day before to create a new Month-to-Date value.

Thank you for you help. I apologize that I wasn't able to determine the answer to my questions from the first perusal of those other articles.

 

[Cosmos75]

RunningSum: (Select Sum([Actual Ore Tons]) from [qry2 Daily Collected Data Calculations] as a where a.[Month/Day/Year]<=[qry2 Daily Collected Data Calculations].[Month/Day/Year])

I believe that JonK has an explanation on the original thread.

Thank you for you help. I apologize that I wasn't able to determine the answer to my questions from the first perusal of those other articles.

No need to apologize.

If you want to, if you would post a sample database with some dummy data that represents your data I'll help out as much as I can.