[AccessD] Running Sum

Gustav,
Thank you very much, I can stop banging my head against the wall. Couple
of tweaks and display the data in a chart and we're good to go.  Thanks
again.
Ed

Ed Tesiny
EdTesiny at oasas.state.ny.us
 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Gustav Brock
> Sent: Friday, November 02, 2007 2:02 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Running Sum
> 
> Hi Ed
> 
> To calculate a running sum or total, DSum may be used but 
> it's faster to use a subquery.
> Basically looks like this:
> 
>   SELECT 
>     S.TransDate, 
>     S.Amount, 
>       (Select 
>         Sum([Amount]) 
>       From 
>         tblTransaction As T 
>       Where 
>         T.TransDate <= S.TransDate) AS 
>     Balance
>   FROM 
>     tblTransaction AS S
>   ORDER BY 
>     S.TransDate;
> 
> I your case something like this (air code, not tested):
> 
>   SELECT 
>     S.Provider_No As Provnum,
>     S.[Report Date], 
>     Sum(S.RunTot) As DayRunTot, 
>       (Select 
>         Sum([RunTot]) 
>       From 
>         qry_MATS_RunSum4 As T 
>       Where 
>         T.Provider_No = S.Provider_No
>         And
>         T.[Report Date] <= S.[Report Date]) AS 
>     CalRunTot
>   FROM 
>     qry_MATS_RunSum4 AS S
>   GROUP BY
>     S.Provider_No,
>     S.[Report Date]
>   ORDER BY 
>     S.Provider_No,
>     S.[Report Date];
> 
> /gustav
> 
> >>> EdTesiny at oasas.state.ny.us 01-11-2007 20:29 >>>
> Hi List,
> I have a query with admission rates data for August and September by
> providers.  I'm trying to get a running sum across the two months for
> each provider.  Put another way, for August we have an admission rate
> and I want August admissions added to Septembers on 
> Septembers row like
>  
> ProviderNo   Report_Date    CalRunTot    Actual Rates
> 3                    8/1/07            2.46                   
>      2.46
> 3                    9/1/07            3.45                   
>        .99
> 
> 5                    8/1/07            8.21                   
>       8.21
> 
> 5                    9/1/07            9.95                   
>       1.74
>  
> Hope this doesn't get all  mixed up in transmission, current SQL is
> below
>  
> SELECT qry_MATS_RunSum4.Provider_No AS Provnum,
> qry_MATS_RunSum4.Provider_No, qry_MATS_RunSum4.Provider_Name,
> qry_MATS_RunSum4.report_date, qry_MATS_RunSum4.AdRate,
> qry_MATS_RunSum4.RunTot,
> DSum("adrate","qry_MATS_RunSum4","Provider_no<=" & [provnum] & " ") AS
> CalRunTot
> FROM qry_MATS_RunSum4;
>  
> MTIA
> Ed
>  
> Edward P. Tesiny
> Assistant Director for Evaluation
> Bureau of Evaluation and Practice Improvement
> New York State OASAS
> 1450 Western Ave.
> Albany, New York  12203-3526
> Phone:  (518) 485-7189
> Fax:  (518) 485-5769
> Email: EdTesiny at oasas.state.ny.us 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>