Counting Unduplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Christina said:
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.
Click to expand...


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
 
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."
 
Back
Top