Jump to content

query problem


Guest yolip
 Share

Recommended Posts

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;

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...