Running Total - Query

[Guest]

I am attempting to calculate 12 month rolling PPM (part per million defects)

I have joined my concern log and my shipments log using the month() and year(). This concept is good for getting monthly shipments and defects per part per supplier, yet I still need to calculate a rolling number

I used Dsum to calculate my rolling, yet the only problem is it calculates from months 1-12 or 12-1 even though we are in month 2.

Here is the syntax: RunTotal: DSum("sumofship_qty","PPM_Shipments_Defects_12","[part number]=" & [partalias] & " And [dmonth]=" & [monthalias] & ""
(field name) (Another Query) (Field Name) (alias in query) (Field Name) (alias in query

What I am attempting to achieve will look like this

Dmonth Dyear Part Number sumofship_qty Runtota
3 2003 #1 50 5
4 2003 #1 75 12
5 2003 #1 50 17
6 2003 #1 25 20

 

[John Viescas]

You need to include the Year in the criteria of your DSum. What is
"monthalias?" What is the name of the Year field returned by this query?
"Yearalias?" Try this:

RunTotal: DSum("sumofship_qty","PPM_Shipments_Defects_12","[part number]=" &
[partalias] &
" And DateSerial([dyear], [dmonth], 1) >= #" & DateAdd("m", -12,
DateSerial([yearalias], [monthalias], 1)) &
"# And DateSerial([dyear], [dmonth], 1) <= #" & DateSerial([yearalias],
[monthalias], 1) & "#")


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I am attempting to calculate 12 month rolling PPM (part per million defects).

I have joined my concern log and my shipments log using the month() and

year(). This concept is good for getting monthly shipments and defects per
part per supplier, yet I still need to calculate a rolling number.

I used Dsum to calculate my rolling, yet the only problem is it calculates

from months 1-12 or 12-1 even though we are in month 2.

Here is the syntax: RunTotal:

DSum("sumofship_qty","PPM_Shipments_Defects_12","[part number]=" &
[partalias] & " And [dmonth]=" & [monthalias] & "")

(field name)

(Another Query) (Field Name) (alias in query)
(Field Name) (alias in query)

What I am attempting to achieve will look like this:

Dmonth Dyear Part Number sumofship_qty Runtotal
3 2003 #1 50 50
4 2003 #1 75 125
5 2003 #1 50 175
6 2003 #1 25 200
.
.
.
2 2004 #1 50 1000

I know Access is doing what I tell it to do (use months as a control), but

I would like to know a way to work around this and force Access to use a
rolling 12 months, not a YTD 12 months.