Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Distinct Count (XP)

    Hi,

    I have a report that I'm trying to get a count of records by [client name]. When I use =count([client name]) I get the total number but what I need is the total number of distinct records.

    Thanks,
    Leesha

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

    Re: Distinct Count (XP)

    Lets's say your report is based upon a query called [qry rpt Clients].
    You could build another query that counts the number of Clients, eg:
    SELECT [client name], Count(*)
    FROM Tablename
    GROUP BY [client name]
    Then in query [qry rpt Clients] include this latest query joined on Client.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Distinct Count (XP)

    By distinct, I presume you mean unique records. There is a property for queries that can be set to return just the unique records - it is called Unique Records. There is also a property Unique Values, which is mutually exclusive, but you want Unique Records. That in turn creates a SELECT DISTINCTROW statement in the SQL string, and when you sum or count with that property set, you should get the correct values.
    Wendell

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distinct Count (XP)

    Hi Pat and Wendell!

    I should've been more clear. I need a formula for the report vs doing it in the query as if I obtain the unique records in the query it throws off other values. What I'm looking for is a formula that I can use in the report itself that will count the number of unigue [names].

    Thanks,
    Leesha

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Distinct Count (XP)

    In that case I think you will have to put some code behind the report to determine the value and then populate an unbound control. You can probably use the DCount() function but it should be based on a query that returns the unique records for your report data source.
    Wendell

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distinct Count (XP)

    Hi Wendell,

    I will try dcount() as you mentioned. Ive seen that used but was not sure the exact purpose.
    Thanks,
    Leesha

Posting Permissions

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