Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

error. help. :(

Discussion in 'Information Technology' started by Tracy, Jul 28, 2006.

Tags:
  1. Tracy

    Tracy
    Expand Collapse
    Guest

    i tried to run a query in access 2003:
    SELECT COUNT(DISTINCT patservices.patientkey) FROM patservices;
    I received syntax error (missing operator) in query expression
    'COUNT(DISTINCT patservices.patientkey) '
    Then i tried
    SELECT COUNT(DISTINCT (PATSERVICES.PATIENTKEY))
    FROM PATSERVICES;
    I received:Undefined function 'DISTINCT' in expression.

    what did i do wrong? thanks alot!
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 9 May 2006 13:49:48 -0400, "Tracy"
    <tracy.chen@quickpractice.com> wrote:

    >i tried to run a query in access 2003:
    >SELECT COUNT(DISTINCT patservices.patientkey) FROM patservices;
    >I received syntax error (missing operator) in query expression
    >'COUNT(DISTINCT patservices.patientkey) '
    >Then i tried
    >SELECT COUNT(DISTINCT (PATSERVICES.PATIENTKEY))
    >FROM PATSERVICES;
    >I received:Undefined function 'DISTINCT' in expression.
    >
    >what did i do wrong? thanks alot!
    >


    You just assumed that ACCESS SQL is ANSI compliant. It's not. In
    particular, the COUNT (DISTINCT... operation is not supported in
    Access.

    You'll need to use a Subquery:

    SELECT Count(*) FROM(SELECT DISTINCT patservices.patientkey FROM
    patservices);

    John W. Vinson[MVP]
     
  4. Tracy

    Tracy
    Expand Collapse
    Guest

    supper! Thanks alot.
    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:nfq162pnlpkgq3cje9j0id0qo9ohtc8blp@4ax.com...
    > On Tue, 9 May 2006 13:49:48 -0400, "Tracy"
    > <tracy.chen@quickpractice.com> wrote:
    >
    >>i tried to run a query in access 2003:
    >>SELECT COUNT(DISTINCT patservices.patientkey) FROM patservices;
    >>I received syntax error (missing operator) in query expression
    >>'COUNT(DISTINCT patservices.patientkey) '
    >>Then i tried
    >>SELECT COUNT(DISTINCT (PATSERVICES.PATIENTKEY))
    >>FROM PATSERVICES;
    >>I received:Undefined function 'DISTINCT' in expression.
    >>
    >>what did i do wrong? thanks alot!
    >>

    >
    > You just assumed that ACCESS SQL is ANSI compliant. It's not. In
    > particular, the COUNT (DISTINCT... operation is not supported in
    > Access.
    >
    > You'll need to use a Subquery:
    >
    > SELECT Count(*) FROM(SELECT DISTINCT patservices.patientkey FROM
    > patservices);
    >
    > John W. Vinson[MVP]
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 9 May 2006 15:26:47 -0400, "Tracy"
    <tracy.chen@quickpractice.com> wrote:

    >supper! Thanks alot.


    Glad it helped. And I just had supper (eggplant and green pepper
    marinara sauce on capellini, with Parmesean and Romano cheese)...

    John W. Vinson[MVP]
     
  6. access user

    access user
    Expand Collapse
    Guest

    sounds yummy - must try it sometime

    "John Vinson" wrote:

    > On Tue, 9 May 2006 15:26:47 -0400, "Tracy"
    > <tracy.chen@quickpractice.com> wrote:
    >
    > >supper! Thanks alot.

    >
    > Glad it helped. And I just had supper (eggplant and green pepper
    > marinara sauce on capellini, with Parmesean and Romano cheese)...
    >
    > John W. Vinson[MVP]
    >
     

Share This Page