query problem

[Guest yolip]

Hi all, I expect the following query to give me the count (tblRating.Count) of individule Description, but it just adding up the count incrementally. How can i reset the count for each description? it is appreciate if anyone can give me a hand. ThanksYolipSELECT DISTINCT tblRating.Description, Count(AIM_ACHIEV.RATING) AS RatingCount, tblRating.CountFROM (AIM INNER JOIN ((((AIM_ACHIEV INNER JOIN AIM_OBJ_DELIV ON AIM_ACHIEV.OUTPUT_ID = AIM_OBJ_DELIV.ID) INNER JOIN tblRating ON AIM_ACHIEV.RATING = tblRating.Rating) INNER JOIN AIM_OBJ ON AIM_OBJ_DELIV.STRATEGY_ID = AIM_OBJ.ID) INNER JOIN KEYCOUNCIL ON AIM_OBJ.KCID = KEYCOUNCIL.ID) ON AIM.ID = KEYCOUNCIL.AIM_ID) INNER JOIN CSF ON AIM.CSF_ID = CSF.IDWHERE (((FinYearQuarter([QTRDATE]))=[Quarter]) AND ((AIM_OBJ_DELIV.STATUS)=-1) AND ((AIM_OBJ.CORPORATEPLAN)=-1)) OR (((AIM_OBJ.AIPLAN)=-1))GROUP BY tblRating.Description, tblRating.Count, CSF.IDHAVING (((CSF.ID)=[ThemeID]))ORDER BY tblRating.Count;

[justsomeguy]

Well you're just selecting tblRating.Count, you aren't calculating it. You are calculating RatingCount by adding up the the number of times you see AIM_ACHIEV.RATING.I took the liberty to format this thing, all the inline inner joins make it difficult to read. And there are a lot of parentheses in there you don't need. What are you trying to do, get the number of times each distinct description appears?

SELECT DISTINCT tblRating.Description, Count(AIM_ACHIEV.RATING) AS RatingCount, tblRating.CountFROM (  AIM INNER JOIN   (    (      (        (          AIM_ACHIEV INNER JOIN            AIM_OBJ_DELIV ON AIM_ACHIEV.OUTPUT_ID = AIM_OBJ_DELIV.ID        ) INNER JOIN          tblRating ON AIM_ACHIEV.RATING = tblRating.Rating      ) INNER JOIN        AIM_OBJ ON AIM_OBJ_DELIV.STRATEGY_ID = AIM_OBJ.ID    ) INNER JOIN      KEYCOUNCIL ON AIM_OBJ.KCID = KEYCOUNCIL.ID  ) ON AIM.ID = KEYCOUNCIL.AIM_ID) INNER JOIN   CSF ON AIM.CSF_ID = CSF.IDWHERE  (    ((FinYearQuarter([QTRDATE]))=[Quarter]) AND    ((AIM_OBJ_DELIV.STATUS)=-1) AND    ((AIM_OBJ.CORPORATEPLAN)=-1)  ) OR  (((AIM_OBJ.AIPLAN)=-1))GROUP BY tblRating.Description, tblRating.Count, CSF.IDHAVING (((CSF.ID)=[ThemeID]))ORDER BY tblRating.Count;