Running total by month 1

[Datathumper]

Question for the pros.

I am trying to create a graph based on a query where two fields are summed and then grouped by month. I need to have one of the summed fields by a running total by month instead. How would I go about this?

THanks in advance.

 

[JonFer]

Read this as a starting point - thread701-708836.

If you need more guidance, post the SQL for your queries that show the individual totals but not the running total.

 

[Datathumper]

The item that I need the running sum on is the welds nde. I need it to sum all welds nde'd up to the month that it is grouping by...not sure if this all makes sense or not.

Here is the SQL statement

SELECT (Format([DT],"mmm"" '""yy")) AS Expr1, Sum([qry_NDE Failures Job]![Welds Failed])/Sum([qry_NDE Failures Job]![Welds NDE]) AS [Percent Failure], [qry_NDE Failures Job].Baseline FROM [qry_NDE Failures Job] GROUP BY (Format([DT],"mmm"" '""yy")), [qry_NDE Failures Job].Baseline, (Year([DT])*12+Month([DT])-1) ORDER BY (Year([DT])*12+Month([DT])-1);

 

[JonFer]

Do you want a running percentage so you can see a trend:

running total failures / running total welds nde

Or ...

Failures for one month as a percentage of the total weld nde's to date (doesn't seem that useful).

Or ...

Just show the running total welds nde in addition to the monthly percent?

 

[Datathumper]

I am using the information in the query for plotting on a graph. I am trying to calculate a percentage failure of welds over the course of the project. The welds failed needs to be divided by the total welds nde's to date. I am plotting by month so that there is a nice trended line shown. The baseline information in the query is a constant 3% which shows where the failure percentage should be under.

I guess to make a long story short...I think running total failures/running total welds nde as you mentioned.

 

[JonFer]

Make sure [qry_NDE_Failures Job] summarizes your data by month before using it for the running totals. Use the first of the month or something so that it is still a date field (e.g. DateSerial(Year([Dt],Month([Dt],1) as Dt). Then try this untested SQL:

SELECT (Format(a.[DT],"mmm"" '""yy")) AS Expr1, 
        a.[Welds Failed]/a.[Welds NDE] as [Month Pct Failure],
        Sum(b.[Welds Failed])/Sum(b.[Welds NDE]) AS [Rolling Pct Failure], 
     a.Baseline 
FROM [qry_NDE Failures Job] a,
     [qry_NDE Failures Job] b
WHERE b.[DT] <= a.[DT]
GROUP BY (Format(a.[DT],"mmm"" '""yy")), 
       a.[Welds Failed]/a.[Welds NDE],
       a.Baseline,
       a.[DT]
ORDER BY a.[DT] ;

There is no check for division by zero but that can be added after you have the rolling totals working.

 

[Datathumper]

I will give it a try and let you know...thanks for the code and the time spent..

 

[Datathumper]

It works almost flawlessly..!!!!!!!!

The only other thing that I was wondering, right now it shows multiple values per month (as welds nde are entered daily). How could I format the grouping so that it gives a month summary instead of showing a plot point for every day that there was an entry?

Thanks in advance.

 

[Datathumper]

.........scrap my last question, I found the solution.

Thanks again for the awesome help.