Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2011
    Posts
    6

    Question Unanswered: Count fuction weirdness

    I have a query that has two fields/ columns. One is a "count," "descending" column the other is standard in that it is not using any aggregate functions etc. What this query is supposed to do is look in the Causecode field and tell me how many times causecode X occurs in this table and what line section has that count.
    Here is the query:

    SELECT aMasterDev.LineSection, Count(aMasterDev.CauseCode) AS CountOfCauseCode
    FROM aMasterDev
    GROUP BY aMasterDev.LineSection
    HAVING (((Count(aMasterDev.CauseCode))>1))
    ORDER BY Count(aMasterDev.CauseCode) DESC;

    It works fine. I get my line-section and a count column that tells how many times line-section X has a causecode. But if I add the field "Subname" to the query I get different count results... 16 vs 11 for my highest count. If I add the field "CauseDesc" i get an even weirder result. In the design view the order of the fields is: Linesection, Causecode, CauseDesc.

    Thank you,
    Russell

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Count is a Property, not a Function, as you're using it here, and you're confusing the Access Gnomes! I suspect you mean to be using the DCount() function, which is used to count the number times a field has a certain Value, within a subset pf records. Type DCount into Help and have a look!

    Linq ;0)>
    Last edited by Missinglinq; 03-08-11 at 01:10.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2011
    Posts
    6
    Thank you very much. I'll check that out.

  4. #4
    Join Date
    Mar 2011
    Posts
    6
    More info:
    As posted earlier, here is the query with bare necessities --

    SELECT aMasterDev.LineSection, Count(aMasterDev.CauseCode) AS CountOfCauseCode
    FROM aMasterDev
    GROUP BY aMasterDev.LineSection
    HAVING (((Count(aMasterDev.CauseCode))>1))
    ORDER BY Count(aMasterDev.CauseCode) DESC;



    This works. It returns:
    LineSection CountOfCauseCode
    340-111112113 16
    230-111112113 16
    196-104 15
    185-194 15
    185-104 14
    206-104 14
    580-F06 13
    560-F02 13
    530-201202 13
    195-104 13
    405-121122123 13
    185-F19F20 13
    420-104 12

    But, if I add a field... "CauseDesc,"

    SELECT aMasterDev.LineSection, Count(aMasterDev.CauseCode) AS CountOfCauseCode, aMasterDev.CauseDesc
    FROM aMasterDev
    GROUP BY aMasterDev.LineSection, aMasterDev.CauseDesc
    HAVING (((Count(aMasterDev.CauseCode))>1))
    ORDER BY Count(aMasterDev.CauseCode) DESC;


    From this I get back 515 records, whereas before I added "CauseDesc" I had 938 records.

    I checked out Dcount and it uses "count" in its expression much like the query I wrote. I still have to play with it.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Are you saying that 'count' is a field name? If this is true you need to change the name. Count is a Reserved Word in Access, and I expect this is causing your problem.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Mar 2011
    Posts
    6
    In this instance:
    SELECT aMasterDev.LineSection, Count(aMasterDev.CauseCode) AS CountOfCauseCode

    Count is a function being used on the CauseCode field, it works. It stops working when I add another field to that query.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    COUNT is indeed a SQL aggregate function.

    Now, do you really need to GROUP BY CauseDesc too or did you add it there because all columns in the SELECT part of an aggregate query need to be members of an aggregate function?

    If so you could try using a dummy aggregate function for CauseDesc that will left its contents unchanged but will comply with the rule. Something like:
    Code:
    SELECT aMasterDev.LineSection, Count(aMasterDev.CauseCode) AS CountOfCauseCode, Min(aMasterDev.CauseDesc)
    FROM aMasterDev
    GROUP BY aMasterDev.LineSection
    HAVING (((Count(aMasterDev.CauseCode))>1))
    ORDER BY Count(aMasterDev.CauseCode) DESC;
    Have a nice day!

  8. #8
    Join Date
    Mar 2011
    Posts
    6
    No, I don't need to Group By CauseDesc. That just happened. I'll look into your example and see what happens. Thank you

  9. #9
    Join Date
    Mar 2011
    Posts
    6
    Thank you peeps... I appreciate the help. Sinndho, I see what you were getting at. With the aggregate function in the query the newly added fields had no way of knowing which element of the aggregate (count) to pick since these fields were not homogenous. As you wrote, i could use the min func with the added date field because the query picked one element, the minimum from the date. My solution was to left join my count query with the table that the counts came from, joined on Linesection. This gets my counts of outages by causecode and I can add whatever other fields I want from the source table.
    Thanks again,

    Lothar

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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