Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Richmond, Virginia, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count distinct in a report? (Access 2002)

    Is it possible to have in a report a Count textbox control that counts only distinct (unique) occurrances of a particular value in a field? I'm reporting incidents that may have multiple associated activities. I must show all the activities, but I also want to count the total number of incidents. I guess the question might be whether SQL or its equivalent is allowed in a textbox expression field.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Count distinct in a report? (Access 2002)

    You could do it in the query that is the source of the report, by defining a field like this:
    iif(Field1="some condition",1,0) as Field1Cntr
    You could use a =Sum(Field1Cntr) for this in the report.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Location
    Richmond, Virginia, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count distinct in a report? (Access 2002)

    What condition would find only the unique values?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Count distinct in a report? (Access 2002)

    Sorry I misunderstood you.
    Use the DISTINCT word in the query.
    Pat

  5. #5
    New Lounger
    Join Date
    Feb 2001
    Location
    Richmond, Virginia, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count distinct in a report? (Access 2002)

    Thanks! But where exactly does DISTINCT go? Do I need to view the query in SQL view?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count distinct in a report? (Access 2002)

    Either that or open the properties dialog on the query itself from the query grid and set Unique Values to Yes. That will insert the DISTINCT keywork into the SQL. To put it in yourself, the syntax is "SELECT DISTINCT ...".
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •