Professional Web Applications Themes

Needed, COUNT(DISTINCT *) workaround for ACCESS - Macromedia ColdFusion

I have a database that contains articles written by different departments. I am trying to find the distinct number of departments and count them so I know how many rows I have to populate another query using maxrows. I have tried: <CFQUERY NAME="numb" DATASOURCE="design"> SELECT COUNT(DISTINCT department_id ) AS deptCount FROM articles </cfquery> I understand that this code will not work with Microsoft Access. I can get the Count or the Distinct to work seperately, but not together, which is what I need. I am pulling out, what's left of, my hair. Does anybody have any suggestions? Rick...

  1. #1

    Default Needed, COUNT(DISTINCT *) workaround for ACCESS

    I have a database that contains articles written by different departments. I am
    trying to find the distinct number of departments and count them so I know how
    many rows I have to populate another query using maxrows. I have tried:

    <CFQUERY NAME="numb" DATASOURCE="design">
    SELECT COUNT(DISTINCT department_id ) AS deptCount
    FROM articles
    </cfquery>

    I understand that this code will not work with Microsoft Access. I can get
    the Count or the Distinct to work seperately, but not together, which is what I
    need.

    I am pulling out, what's left of, my hair. Does anybody have any suggestions?

    Rick

    rickaclark54 Guest

  2. #2

    Default Re: Needed, COUNT(DISTINCT *) workaround for ACCESS

    [url]http://forums.aspfree.com/archive/t-35077/SQL-Count-distinct-in-ACCESS-DB[/url]

    --
    Ken Ford
    PVII Support Team
    [url]http://www.projectseven.com[/url]
    Team Macromedia Volunteer - Dreamweaver
    Certified Dreamweaver MX 2004 Developer


    "rickaclark54" <webforumsusermacromedia.com> wrote in message news:d149fg$qj8$1forums.macromedia.com...
    >I have a database that contains articles written by different departments. I am
    > trying to find the distinct number of departments and count them so I know how
    > many rows I have to populate another query using maxrows. I have tried:
    >
    > <CFQUERY NAME="numb" DATASOURCE="design">
    > SELECT COUNT(DISTINCT department_id ) AS deptCount
    > FROM articles
    > </cfquery>
    >
    > I understand that this code will not work with Microsoft Access. I can get
    > the Count or the Distinct to work seperately, but not together, which is what I
    > need.
    >
    > I am pulling out, what's left of, my hair. Does anybody have any suggestions?
    >
    > Rick
    >

    Ken Ford - *TMM* & PVII Guest

  3. #3

    Default Re: Needed, COUNT(DISTINCT *) workaround for ACCESS

    Just run your Distinct query (I assume you want the list of departments anyway) and then the CF variable yourquery.RecordCount will tell you how many departments were returned.
    JMGibson3 Guest

  4. #4

    Default Re: Needed, COUNT(DISTINCT *) workaround for ACCESS

    Thanks, that did the trick. I was trying to make it too difficult.

    Rick
    rickaclark54 Guest

  5. #5

    Default Re: Needed, COUNT(DISTINCT *) workaround for ACCESS

    Thanks, that did the trick. I was trying to make it too difficult.

    Rick
    rickaclark54 Guest

Similar Threads

  1. MS Access Query Help Needed
    By hertelt in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 2nd, 10:57 PM
  2. distinct, count
    By Frances in forum MySQL
    Replies: 3
    Last Post: December 2nd, 07:42 PM
  3. Workaround needed for: Request.ServerVariables("HTTP_Referer")
    By James Brown in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 15th, 03:13 PM
  4. Select DISTINCT issue (SQL Expert needed)
    By BP Prgm in forum ASP Database
    Replies: 2
    Last Post: October 2nd, 01:36 AM
  5. retrict page access count
    By Ian in forum ASP
    Replies: 1
    Last Post: August 10th, 02:48 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139