SQL Query troubles

Using MS Access 2002.



As far as I can tell that should work, but it throws up this error :



What I am trying to do is count where rating = 1 for ratings.pId = & pId &, where rating = 2 for the same pId, and where rating = 3 for the same pId, all in the same query. I've done some googling and it should be possible using that but its throwing a fit !

This is the schema :

table : RATINGS
rId (primary key)
pId
rActive
rComment
rUser
rUserEmail
rRatingId (==ratingsNames.rRatingId)

table : RATINGSNAMES
rRatingId (primary key)
rRatingName

I am trying to avoid having to do three separate SQL queries against the database as, let's face it, Access can be slow enough anyway.

Can anyone show me the error of my ways ? Thanks in advance.

replace

case when ratings.rRatingId = 1 then 1 else 0 end

with

iif(ratings.rRatingId = 1, 1, 0)

microsoft access has its own sql language

rudy
http://r937.com/

access does not support case expressions, you have to use the iif function instead.

e.g.
iif(rating=1,1,0)

Thanks guys, now using :

SELECT Count(iif(ratings.rRatingId = 1, 1, 0)) as countNeg, Count(iif(ratings.rRatingId = 2, 1, 0)) as countNeut, Count(iif(ratings.rRatingId = 3, 1, 0)) as countPos FROM ratings WHERE ratings.pId = " & pId

And its working perfectly.

I'll check around for some info on Access' own peculiar mix of SQL so I hopefully don't run into this again

Oh no, its not working quite right. There are three total rows in the ratings table; I want it to only count on the condition that rRatingId = 1 ~ 3 as appropriate, which means countNeg, countNeut and countPos should (currently) be at 1, 1, 1 instead of 3, 3, 3.

Could I ask you to give me some input again ?

you probably want sum() instead of count()

think about it -- count three 1s or 0s, and how many of them are there? three

:-)

Doesn't Sum() add together the values of the field, and Count() count the number of rows returned ?

Hmm evidently not as it seems to do what I want Thanks.