Re: Count Distinct

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: SLEJ (SLEJ_at_discussions.microsoft.com)
Date: 02/20/05 

Date: Sun, 20 Feb 2005 12:53:03 -0800

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

"Jeff Boyce" wrote:

> 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>
>
> "SLEJ" <SLEJ@discussions.microsoft.com> wrote in message
> news:9CA68D9B-A289-400E-9CA5-CE3544DDC8BE@microsoft.com...
> > 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
> >
> > "Jeff Boyce" wrote:
> >
> > > 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>
> > >
> > > "SLEJ" <SLEJ@discussions.microsoft.com> wrote in message
> > > news:2C475CBC-EC3D-46E4-9AD9-FF61BB75E9E0@microsoft.com...
> > > > 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.
> > >
> > >
>
>

Relevant Pages

  • Re: Abs(sum()) or DCount()
    ... I tried to used Abs) but it generate errors, since I tried the Dcount ... and it work when counting record hen I put it at page footer, ... Jeff Boyce wrote: ... Microsoft Office/Access MVP ...
    (microsoft.public.access.reports)
  • Re: Transfer text with count
    ... Another would be to use the DCount() function. ... Check Access HELP for help on these... ... Microsoft Office/Access MVP ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.macros)
  • Re: Counting Records in a Report
    ... "Jeff Boyce" wrote: ... Or take a look at using the DCount() function... ... type I also have a Returned field which ... For example: out of the 7k records I want the report to show: ...
    (microsoft.public.access.reports)