Count Distinct

[Guest]

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The text box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank you.

 

[Jeff Boyce]

How? As in "how are you (successfully) counting total enrollments?", and
"how are you trying to count unique clients?"

Are you using a query? Are you using a Count() expression in your report?
Are you using a DCount() expression in your report? What's working for you
now?

--
More info, please ...

Jeff Boyce
<Access MVP>

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The text box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank

you.

 

[Guest]

Jeff,

1. The report is based on a query combining the Population Table and the
Enrollment Table.
2. to count total enrollments, I created a calculated text box with the
source equal to: =Count([enrollmentID])
3. right now I have a calculated text box for the unique clients count
field equal to: =Count([participantID]). This doesn't work (well, it
calculates but not how I want it) but I put this in just to have something to
play around with. I'd like to have the control count only the first
occurance of each participant ID which would give me the total number of
unique clients.

Thanks.
- Sharon

How? As in "how are you (successfully) counting total enrollments?", and
"how are you trying to count unique clients?"

Are you using a query? Are you using a Count() expression in your report?
Are you using a DCount() expression in your report? What's working for you
now?

--
More info, please ...

Jeff Boyce
<Access MVP>

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The text box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank

you.

 

[Jeff Boyce]

Sharon

If the query joins the two tables, you'll have as many participantIDs as
there are valid combinations (your count of enrollments). Consider using
the DCount() function in a text field to count the number of participantIDs
in the Population table.

--
Good luck

Jeff Boyce
<Access MVP>

Jeff,

1. The report is based on a query combining the Population Table and the
Enrollment Table.
2. to count total enrollments, I created a calculated text box with the
source equal to: =Count([enrollmentID])
3. right now I have a calculated text box for the unique clients count
field equal to: =Count([participantID]). This doesn't work (well, it
calculates but not how I want it) but I put this in just to have something to
play around with. I'd like to have the control count only the first
occurance of each participant ID which would give me the total number of
unique clients.

Thanks.
- Sharon

How? As in "how are you (successfully) counting total enrollments?", and
"how are you trying to count unique clients?"

Are you using a query? Are you using a Count() expression in your report?
Are you using a DCount() expression in your report? What's working for you
now?

--
More info, please ...

Jeff Boyce
<Access MVP>

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem

to
get

the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The

text
box

control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank

you.

 

[Guest]

Jeff,

I'm not sure what DCount is suppose to do but I did what you suggested and I
still must be doing something wrong because it didn't work.

In the report (the footer), for the field to count the unique IDs, as the
source I entered: =Dcount("[participantID]","2Population") {2Population is
the name of the table containing participant ID}.

The result was the doubling of the number of records. That is, I was
getting 11 before (which was wrong) and now I'm getting 22.

My frustration is mounting over something that should be very simple for
Acces to do but I'm just not getting it. Thanks.

- Sharon

Sharon

If the query joins the two tables, you'll have as many participantIDs as
there are valid combinations (your count of enrollments). Consider using
the DCount() function in a text field to count the number of participantIDs
in the Population table.

--
Good luck

Jeff Boyce
<Access MVP>

Jeff,

1. The report is based on a query combining the Population Table and the
Enrollment Table.
2. to count total enrollments, I created a calculated text box with the
source equal to: =Count([enrollmentID])
3. right now I have a calculated text box for the unique clients count
field equal to: =Count([participantID]). This doesn't work (well, it
calculates but not how I want it) but I put this in just to have something to
play around with. I'd like to have the control count only the first
occurance of each participant ID which would give me the total number of
unique clients.

Thanks.
- Sharon

How? As in "how are you (successfully) counting total enrollments?", and
"how are you trying to count unique clients?"

Are you using a query? Are you using a Count() expression in your report?
Are you using a DCount() expression in your report? What's working for you
now?

--
More info, please ...

Jeff Boyce
<Access MVP>

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to
get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique
Clients
should be 10 and the Total Number of Enrollments should be 11. The text
box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank
you.

 

[Marshall Barton]

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The text box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank you.

A different approach would be to have the report group on
the client field. Then add a text box named txtClientCount
to the group header. Set its ControlSource expression to =1
and its RunningSum property to Over All. Make the group
header section invisible if you have no other use for it.

Your report footer text box can then display the number of
unique clients by using the expression =txtClientCount

 

[Guest]

Marsh,

You're my new best friend! That worked. Thanks.

- Sharon

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The text box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank you.

A different approach would be to have the report group on
the client field. Then add a text box named txtClientCount
to the group header. Set its ControlSource expression to =1
and its RunningSum property to Over All. Make the group
header section invisible if you have no other use for it.

Your report footer text box can then display the number of
unique clients by using the expression =txtClientCount

 

[Guest]

Logically, I can't think of why the DCount would not work on Reports.

But your trick did it!!! thanks a bunch!!!

I'm creating a report where I want to count 2 things: Number of Unique
Clients and Total Number Of Enrollments. A client can have multiple
enrollments.

I can do the counting of total number of enrollments but I can't seem to get
the text box for counting the number of unique clients to calculate
correctly.

For example: the database contains 10 clients with 1 client having 2
enrollments and all others just 1 enrollment. The Number of Unique Clients
should be 10 and the Total Number of Enrollments should be 11. The text box
control for the Unique Clients is called [participantID].

How do I get ParticipantID to count only 10 records and not 11? Thank you.

A different approach would be to have the report group on
the client field. Then add a text box named txtClientCount
to the group header. Set its ControlSource expression to =1
and its RunningSum property to Over All. Make the group
header section invisible if you have no other use for it.

Your report footer text box can then display the number of
unique clients by using the expression =txtClientCount