Running total - Monthly

[Hallel86]

I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month. I feel like I am close, but I have been working on it all day and still haven't found my answer.:banghead:

I attached a PDF with an example of what I am working with. Any help would be greatly appreciated. Thanks!

 

[JHB]

Show you query, and how you want the result.

 

[Mihail]

The key is to split the Date into YEAR and MONTH.
I have attached a small database that normalized your table.

 

[Hallel86]

Basically The last collum in my PDF is what I am looking for result wise. I have this so far, but I can not figure out why the numbers are not coming out correct, or if I am even close at all, lol

SELECT DD1.WELL_ID, DD1.Well_Name, DD1.RecordDate, DD1.DailyUpTime, DSum("[DailyUpTime]","[Q_DailydownHour1]","Format(RecordDate,'yyyymm') = '" & Format([RecordDate],'yyyymm') & "' AND [RecordDate]-1 < #" & [RecordDate] & "#") AS MTDHours
FROM Q_DailyDownHour1 AS DD1;

 

[JHB]

You are missing the sort order -> "Order by".

SELECT DD1.WELL_ID, DD1.Well_Name, DD1.RecordDate, DD1.DailyUpTime, DSum("[DailyUpTime]","[Q_DailydownHour1]","Format(RecordDate,'yyyymm') = '" & Format([RecordDate],'yyyymm') & "' AND [RecordDate]-1 < #" & [RecordDate] & "#") AS MTDHours
FROM Q_DailyDownHour1 AS DD1 Order By DD1.WELL_ID, DD1.Well_Name, DD1.RecordDate;

 

[Hallel86]

That was helpful. I believe there is still something wrong with my code. I get results that are un-explainable by myself. Here are the results I get. Naturally, the last field is the running total.

 

[Mihail]

I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.

Sorry for misunderstanding. In fact you wish a Total after every new Uptime input but limited at the month when this input occurred. Have I understand well this time ?

And for my English: This is the meaning for running total expression ? Thank you.

 

[Mihail]

And this must be the solution (hope).
Something seems to going wrong in yours computation. Verify, please.

 

[Hallel86]

Yours works perfect! I am not sure about the "yourstotal" field, but everything else works well! Would you happen to know how to do this in SQL? I do not know VBA at all, so it is difficult for me to read.

 

[Mihail]

And I don't know SQL.
So we need the 3rd part help.

Cheers.

 

[JHB]

The SQL is:

SELECT WellData.Well_ID, WellData.Well_Name, WellData.RecordDate, WellData.DailyUpTime, Sum(WellData2.DailyUpTime) AS MTDHours
FROM WellData AS WellData2 INNER JOIN WellData ON WellData2.Well_ID = WellData.Well_ID
WHERE (((WellData2.RecordDate)<=[WellData].[recorddate]))
GROUP BY WellData.Well_ID, WellData.Well_Name, WellData.RecordDate, WellData.DailyUpTime
ORDER BY WellData.Well_ID, WellData.RecordDate;

The result is shown below: