Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Counting distinct values in a report

    I am working on a report that needs to calculate the distinct number of clients in each grouping level. The grouping levels are Client, City, County, and Entire report. For this count I only need the distinct counts in the City, County, and entire footers.

    The problem that I have is that everything I try results in a count of the records involved, not the distinct count of clients.

    Any ideas?

    Data example

    Client City County
    1 City1 County1
    2 City1 County1
    2 City1 County1
    14 City2 County1
    16 City3 County2
    16 City3 County2

    So with this example:
    City1 would have a count of 2 clients
    City2 1 client
    City3 1 client
    County1 3 clients
    County2 1 client
    entire report 4 clients

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yeah ... Use SELECT DISTINCT in your query ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Tried that in a variety of ways and it did not work. There are over a dozen fields in the query that drives this report and I have already got it down to distinct values across those fields.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And can we assume that you have groupings on each of your target columns in the order that you desire in the report?

    Also, your query is returning multiple rows of those grouped attributes (thus generating this question) correct?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by baolive
    Tried that in a variety of ways and it did not work. There are over a dozen fields in the query that drives this report and I have already got it down to distinct values across those fields.
    Then the data example you gave is not accurate. Try again.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Then the data example you gave is not accurate. Try again.
    Getting blunter and blunter in your old age there Ted???
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    No the data example did not include all of the columns in the query, just those that data is being grouped on.

    Each client can have multiple data records, and in the final report the actual detail records are irrelevent, and the detail section of the report is not visible.

    It is the counts of the various columns that is important, but for this one column (which just got tossed at me when I thought I was done) we only want to count distinct values.

    This report is counting two different types of services, one of which is broken down into four subcategories, each of which performs 3 counts (events, days during the report period, avg days)

    The actual data is in SQL server and I have combined the two tables containing the data fields with a view using a union all statement (substituting null values where needed).

    So an actual line of data that the report would have the following

    clientid, esdate, facility, ss, ssdays, ssavg, x, xdays, savg, oh, ohdays, ohavg, o, odays, oavg, city, county

    In this data esdate is the first item "type" that is being counted, with the next twelve fields all being the same "type" just presorted out into the appropriate category. City and county are the grouping levels.

    As you can imagine the data has a lot of null values.

Posting Permissions

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