Results 1 to 5 of 5
  1. #1
    SwensrnTaz is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    3

    Request for an Example of eCount SQL

    I am attempting to do an eCount in Access to return a sum of unique values. I have 1257 records but I know through a physical count, I really only have 315 unique values.



    See attached file:

    In Column A, I know there are 36 unique Work_IDs - I need an eCount syntax that would provide this.

    Then, I need to count the Total number of Work_IDs where the Project Adoption Level only is 1 or 2; or the Assessment Status for the Work_ID is only Closed.

    work_id #_Phases_Assessed Assessment Status Project Adoption Level
    20545 1 Closed 1
    20549 2 Closed 1
    20550 1 Closed 1
    20551 1 Closed 1
    20553 2 Remediation
    20560 2 Closed 1
    20571 1 Closed 1
    20571 2 Remediation
    20572 1 Closed 1
    20572 3 Remediation
    20573 2 Closed 1
    20573 2 Remediation
    20574 1 Closed 1
    20574 1 Remediation
    20576 1 Closed 1
    20576 1 Remediation
    20578 4 Closed 1
    20582 3 Remediation
    20585 1 Closed 1
    20589 1 Closed 1
    20592 1 Closed 1
    20602 2 Closed 1
    20614 1 Closed 1
    20633 1 Closed 1
    20644 1 Closed 1
    20646 1 Closed 1
    20647 1 Closed 1
    20680 2 Closed 1
    20680 1 Remediation
    20682 2 Closed 1
    20682 1 Remediation
    20703 1 Remediation
    20711 1 Closed 1
    20711 1 Remediation
    20712 3 Remediation
    20817 1 Remediation
    20864 3 Remediation
    21157 2 Remediation
    21158 2 Remediation
    21159 2 Closed 2
    21161 2 Closed 2
    21164 2 Remediation
    21322 1 Remediation
    21509 1 Remediation


    Can someone assist with this please?

    Thanks

    Randy

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't know what 'eCount' is but sounds like you need to do aggregate (Totals or GROUP BY) query. Access Help has guidelines on building Totals query. Basically, query in design view, click the Totals button (looks like sigma) on ribbon. This modify the query design grid for grouping and aggregate calcs.

    Or build reports using Grouping & Sorting with aggregate calcs. A report will allow display of detail records and still do the aggregate calcs. Again, Access Help has guidelines.
    Last edited by June7; 03-12-2012 at 02:42 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    SwensrnTaz is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    3
    When I do a Count, dCount or eCount it returns the number of instances for the value and not the number of unique values. I am attempting to count the number of unique values (where each value may appear 1, 2, 3 or 4 times) in a list. I know there are 1257 record but of those 1257 records, there are only 315 unique values.

    Then, the next count is when those unique values meet a certain value under the Assessment Status field. I know there are only 36 total unique values in that list, but for some reason it is returning 44.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "ECount()" (by Allen Browne) is a replacement function for the "DCount()" VBA function.

    http://allenbrowne.com/ser-66.html

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by SwensrnTaz View Post
    When I do a Count, dCount or eCount it returns the number of instances for the value and not the number of unique values. I am attempting to count the number of unique values (where each value may appear 1, 2, 3 or 4 times) in a list. I know there are 1257 record but of those 1257 records, there are only 315 unique values.

    Then, the next count is when those unique values meet a certain value under the Assessment Status field. I know there are only 36 total unique values in that list, but for some reason it is returning 44.
    Randy,

    Using your example data, I used these two ECounts() that seems to return the correct values. I used "Randy" for the table name and deleted the spaces in your field names. (FYI, it is not a good idea to use spaces or special characters in object names...)
    Change "Randy" to the name of your table or query:

    MsgBox "Distinct Work Id's = " & ECount("work_id", "Randy", "", True)

    MsgBox "ProjectAdoptionLevel = " & ECount("ProjectAdoptionLevel", "Randy", "ProjectAdoptionLevel = 1 or ProjectAdoptionLevel = 2", False)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Request to check relationships
    By adohertyd in forum Database Design
    Replies: 13
    Last Post: 12-19-2011, 04:18 PM
  2. Replies: 5
    Last Post: 05-20-2011, 06:18 AM
  3. Number operation request.
    By Playerpawn in forum Access
    Replies: 0
    Last Post: 05-18-2011, 11:29 AM
  4. Replies: 1
    Last Post: 11-23-2010, 10:41 AM
  5. request username and password
    By meysam_e2006 in forum Access
    Replies: 2
    Last Post: 07-02-2010, 02:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums