Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: count with MS access database

    Hi,
    I run this query in Oracle:
    SELECT MACROSERVIZIO, AREA_TERRITORIALE, count(distinct MATRICOLA) count_matricole,
    ROUND(SUM(PUNTEGGIO_NEW*PESO_RISPOSTA)/SUM(PESO_RISPOSTA),2) AS MEDIA,
    ROUND(100-(1-(SUM(PUNTEGGIO_NEW*PESO_RISPOSTA)/SUM(PESO_RISPOSTA)))/-0.05,2) AS PERCENTILE
    FROM CS_RISP_RIC_DISTR A, CS_MATRICOLA B, CS_SERVIZINOMICOLONNE c
    WHERE USERID=MATRICOLA And A.PROG_DOMANDA=C.PROG_DOMANDA
    and punteggio_new is not null
    GROUP BY MACROSERVIZIO, AREA_TERRITORIALE

    Now I'd like to run same query on Access, but I get this error:

    Sintax error (missing operator) in query expression 'count(disitnct matricola)'

    How can I write same query with Access database?

    Thanks in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, let's break this down.

    What happens when you run the following in Access
    Code:
    SELECT SELECT MACROSERVIZIO
         , AREA_TERRITORIALE
         , count(distinct MATRICOLA) count_matricole
    FROM CS_RISP_RIC_DISTR A, CS_MATRICOLA B, CS_SERVIZINOMICOLONNE c
    WHERE USERID=MATRICOLA And A.PROG_DOMANDA=C.PROG_DOMANDA
    and punteggio_new is not null
    GROUP BY MACROSERVIZIO, AREA_TERRITORIALE
    Moved to Access topic.
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    If i try:
    select MACROSERVIZIO, AREA_TERRITORIALE, count(matricola)
    from
    (
    SELECT DISTINCT MACROSERVIZIO, AREA_TERRITORIALE, MATRICOLA, ROUND(SUM(PUNTEGGIO_NEW*PESO_RISPOSTA)/SUM(PESO_RISPOSTA),2) AS MEDIA, ROUND(100-(1-(SUM(PUNTEGGIO_NEW*PESO_RISPOSTA)/SUM(PESO_RISPOSTA)))/-0.05,2) AS PERCENTILE
    FROM CS_RISP_RIC_DISTR AS A, CS_MATRICOLA AS B, CS_SERVIZINOMICOLONNE AS c
    WHERE USERID=MATRICOLA And A.PROG_DOMANDA=C.PROG_DOMANDA
    and punteggio_new is not null
    GROUP BY MACROSERVIZIO, AREA_TERRITORIALE, MATRICOLA
    )
    group by MACROSERVIZIO, AREA_TERRITORIALE;

    I get correct value of count(matricola)


    but I also need columns "media" and "percentile" and when I add media and percentile:

    select MACROSERVIZIO, AREA_TERRITORIALE, MEDIA, PERCENTILE
    from
    (
    SELECT DISTINCT MACROSERVIZIO, AREA_TERRITORIALE, MATRICOLA, ROUND(SUM(PUNTEGGIO_NEW*PESO_RISPOSTA)/SUM(PESO_RISPOSTA),2) AS MEDIA, ROUND(100-(1-(SUM(PUNTEGGIO_NEW*PESO_RISPOSTA)/SUM(PESO_RISPOSTA)))/-0.05,2) AS PERCENTILE
    FROM CS_RISP_RIC_DISTR AS A, CS_MATRICOLA AS B, CS_SERVIZINOMICOLONNE AS c
    WHERE USERID=MATRICOLA And A.PROG_DOMANDA=C.PROG_DOMANDA
    and punteggio_new is not null
    GROUP BY MACROSERVIZIO, AREA_TERRITORIALE, MATRICOLA
    )
    group by MACROSERVIZIO, AREA_TERRITORIALE, MEDIA, PERCENTILE;

    I get too much values and incorrect values.

    How can I resolve this problem?

Posting Permissions

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