ACCESS FEED: Re: [MS_AccessPros] Any suggestions for creating running cumulative sums?

Senin, 22 Juni 2015

Re: [MS_AccessPros] Any suggestions for creating running cumulative sums?

Why not just use the Running Sum feature in a Report?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jun 23, 2015, at 6:50 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I would use a couple subqueries for the totals. Generically:

SELECT fieldA, fieldB, DateA, NumberA, NumberB,
(SELECT Sum(NumberA) FROM YourTable B WHERE B.FieldA = YourTable.FieldA AND B.DateA <= YourTable.DateA) As NumberARunningTotal
FROM YourTable;

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 22 Jun 2015 19:23:01 -0700
Subject: [MS_AccessPros] Any suggestions for creating running cumulative sums?

I want to create some cumulatives based on WO# in date sequence for both quantity and time. Everything I've tried so far has been very brute force, with complete lack of elegance and prone to failure.

Any suggestions? I'd really appreciate it.

Here is some sample data:

WO#	Date_	Qty	Cum Qty	Minutes	Cum Minutes
5476401B-30	02-Jan-15	250	250	3690	3690
5476401B-30	05-Jan-15	158	408	3405	7095
5476401B-30	06-Jan-15	0	408	1545	8640
5476601B-20	06-Jan-15	0	0	2580	2580
5476601B-20	07-Jan-15	406	406	3765	6345
5476601B-20	08-Jan-15	453	859	3675	10020
5476601B-20	09-Jan-15	316	1175	3495	13515
5476601B-20	12-Jan-15	443	1618	4980	18495
5476601B-20	13-Jan-15	501	2119	5265	23760
5476601B-20	14-Jan-15	502	2621	4515	28275
5476601B-20	15-Jan-15	185	2806	2460	30735
5477801B-30	15-Jan-15	136	136	2295	2295
5477801B-30	16-Jan-15	0	136	4545	6840
5477801B-30	19-Jan-15	287	423	4095	10935
5477801B-30	20-Jan-15	0	423	3015	13950