Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    16

    Unanswered: MS ACCESS COUNT function

    When I used Count () function to count distinct rows in MS Access, I got the error for the syntax of this Count expression.

    Here is the query:

    SELECT ColA, COUNT(DISTINCT ColB)
    FROM TAB1
    GROUP BY ColA

    I issued the above query in DB2 and there is no error. Anyone has any idea why Access complains expression syntax of the above query?

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Posts
    1

    Re: MS ACCESS COUNT function

    You can't use DISTINCT keyword here.

    Originally posted by alex_db
    When I used Count () function to count distinct rows in MS Access, I got the error for the syntax of this Count expression.

    Here is the query:

    SELECT ColA, COUNT(DISTINCT ColB)
    FROM TAB1
    GROUP BY ColA

    I issued the above query in DB2 and there is no error. Anyone has any idea why Access complains expression syntax of the above query?

    Thanks!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you really need it, alex, there is a way to do this with a microsoft access "view" i.e. query of query

    however, you need to confirm that you really want select colA, count(distinct colB) rather than select colA, count(colB) because they are different

    what you do is define a query like select distinct colA, colB and then save this, after which you can do select colA, count(colB) from savedquery

    if this isn't clear, tell me your columns and i'll rework the queries


    rudy

Posting Permissions

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