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 
(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_QtyCum QtyMinutesCum Minutes
5476401B-3002-Jan-1525025036903690
5476401B-3005-Jan-1515840834057095
5476401B-3006-Jan-15040815458640
5476601B-2006-Jan-150025802580
5476601B-2007-Jan-1540640637656345
5476601B-2008-Jan-15453859367510020
5476601B-2009-Jan-153161175349513515
5476601B-2012-Jan-154431618498018495
5476601B-2013-Jan-155012119526523760
5476601B-2014-Jan-155022621451528275
5476601B-2015-Jan-151852806246030735
5477801B-3015-Jan-1513613622952295
5477801B-3016-Jan-15013645456840
5477801B-3019-Jan-15287423409510935
5477801B-3020-Jan-150423301513950


Buck Crouch   
CFPIM CIRM CSCP CSM MBA
Managing Partner                                       
1 Delta, LLC
Supply Chain & Manufacturing Experts
1 E. Delta Rd, Tucson, AZ 85706
520.668.7095    Buck@1Delta.net



__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

.

__,_._,___

Tidak ada komentar:

Posting Komentar