Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Distinct Count in a report

    I am trying to count the number of unique records, or a distinct count, of a number of records that fall in each group in a report.

    Group 1
    CustomerID
    AAA001
    AAA001
    ABC001
    CBA001

    Here I have 4 records falling in the group but these relate to only 3 customers. I want the count to show 3.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A pain in the pooper in Access. I think if you change the ANSI setting in options you can use:
    Code:
    SELECT COUNT(DISTINCT CustomerID) AS cust_count FROM myTable
    otherwise:
    Code:
    SELECT COUNT(*) AS cust_count FROM (SELECT DISTINCT CustomerID FROM myTable) AS distinct_customers

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I am unable to get this working thou. I am a little nervous about changing the ANSI settings as I have so many other queries running and I an not sure what affect this will have on them.

    Do you know of any other options on this one?

    Thanks again

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I gave you two

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so an alternative way consider doing it in Access, rather than using SQL. it may even be more efficient and quicker than using SQL here and there.

    open the report in design mode
    add a control in each footer as required, with label if needed
    switch to the code window
    declare a variable(s) to hold the sum for each required grouping
    set each variable to 0 in each relevant group header
    add 1 to each group variable in the forms detail event
    assign the relevant value to the relevant group footer

    ..it does mean you have to be prepared to dive into a bit of code
    it does mean you have to be prepared to have a play with report design structure
    it does mean you have have made a start form letting Access do everything to you and do more for yourself.

    In this case its a fairly trivial example, but manipulating code behind the access UI is a powerful technique

    http://www.dbforums.com/showthread.p...+detail+footer

Posting Permissions

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