[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