[AccessD] Cumulative sum

Ed
It depends on how the ReportDate is created. Is it a full real date or
say just the beginning of each month.  Do you want each month summed or
all three together?  Is ActiveClients a count or Indicator?

I assume this is Access gui
1) IF real date and want each month and ActiveClients is count
    SELECT  ProviderNo,
            FORMAT(ReportDate,"MM/YYYY") as RptDt,
                         or FORMAT(ReportDate, "MM/01/YYYY"
            SUM(ActiveClients) as ActClient
    FROM Tbl1
    GROUP BY ProviderNo,
             FORMAT(ReportDate,"MM/YYYY") 
 
2) IF real date and want each month and activeclient is indicator
     SELECT  ProviderNo,
             FORMAT(ReportDate,"MM/YYYY") as RptDt,
                         or FORMAT(ReportDate, "MM/01/YYYY"
             SUM (IIF(ActiveClients = TRUE,1,0) as ActClient
    FROM Tbl1
    GROUP BY ProviderNo,
             FORMAT(ReportDate,"MM/YYYY") 

I didn't have time to fully test this but it is what I remember
HTH 
Patti   
**************************************************
* Patricia O'Connor
* Associate Computer Programmer Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at nysemail.state.ny.us
**************************************************
 

> 
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tesiny, Ed
> Sent: Monday, October 29, 2007 11:30 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Cumulative sum
> 
> Hi All,
> I think this should be easy but I can't figure it out.  To 
> simplify, say you have three fields, ProviderNo, ReportDate, 
> ActiveClients.  So, if you have 3 months of data for a 
> provider you can create a chart plotting each month.  Is 
> there a way in a query to get the sum of the 3 months?  
> 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
> 
>