Counting Unduplicates

[Guest]

I am trying to count the number of clients total, unduplicated. The database is set up with a table and a form where you enter information for each counseling session. 1 client may be counseled a number of times. At the end of the quarter we need to report to the gov't the total number of counseling sessions (which I can do), the total number of clients counseled (unduplicated), The total # of hours counseled (of all counseling sessions), who was counseled by internet (unduplicated # of clients counseled by internet), the counseling topic (of all sessions), total hours per counseling topic (of all sessions), the number of counseling sessions per counselor (out of 8 people), and all this needs to be separated by 3 different agencies, us, and our 2 sister agencies.
I know how to count and sum. What I am having trouble with is counting unduplicated values and counting based on a criteria (for example counting the session dates by Counselor name)

Can anyone help me out with this, it would be great. Thanks.

 

[Marshall Barton]

I am trying to count the number of clients total, unduplicated. The database is set up with a table and a form where you enter information for each counseling session. 1 client may be counseled a number of times. At the end of the quarter we need to report to the gov't the total number of counseling sessions (which I can do), the total number of clients counseled (unduplicated), The total # of hours counseled (of all counseling sessions), who was counseled by internet (unduplicated # of clients counseled by internet), the counseling topic (of all sessions), total hours per counseling topic (of all sessions), the number of counseling sessions per counselor (out of 8 people), and all this needs to be separated by 3 different agencies, us, and our 2 sister agencies.
I know how to count and sum. What I am having trouble with is counting unduplicated values and counting based on a criteria (for example counting the session dates by Counselor name).

Can anyone help me out with this, it would be great. Thanks.

You will need to construct a new Totals (Group By) type
query to calculate the unduplicated clients count.

SELECT Count(Clients)
FROM table
GROUP BY Clients

I can't tell for sure, but I think you'll need one or more
additional queries for your other totals. Maybe one like
this??

SELECT Counselor, Client, Sum(Hours)
FROM table
GROUP BY Counselor, Client

 

[Petrucci2000]

Hi,

My name is Eric. Thank you for using the Microsoft Access Newsgroups.

You wrote:
"..I am trying to count the number of clients total, unduplicated.."


Have you tried using a Totals Query with a criteria of count equal to 1?
Example:


Field: CounselingSession CounselingSession
Table: YourTable YourTable
Sort:
Show: Yes No
Criteria:
=1


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."