[AccessD] Access 2k - Report group counter

THANK YOU.

More round-a-bout than I wanted to go but got the job done.  

Would like to be able to count the Person group footer data at the
Location level then the location level at the upper group level. 


**************************************************
* 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 Ken Ismert
> Sent: Wednesday, April 19, 2006 02:02 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access 2k - Report group counter
> 
>  
> Patricia, 
> 
> > I have a report where I want to count the number of unique 
> People in a
> group that received checks ...  
> 
> Well, in SQL Server you have Count(Distinct PEOPLE_ID), which 
> will count the distict people receiving checks. 
> 
> Access has no Count Distict function. To duplicate the 
> function, you have to make a separate query:
> 
>     SELECT qSub.GroupID, Count(qSub.PEOPLE_ID) AS GrpPeopleCt 
>     FROM (SELECT DISTINCT GroupID, PEOPLE_ID FROM tblMyTable) AS qSub 
>     GROUP BY GroupID
> 
> This query simply counts the distict people returned from its 
> subquery, qSub.
> 
> Add this query to your original, and join by your GroupID 
> field to get your GrpPeopleCt value for each group. 
> 
> -Ken
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>