Problem Query return and average value 1

[jamiehook01]

Hi i am trying to write a query which will return and average value for a rating (online photograph album with rating feature)

here is what i have done so far

rs.Open("SELECT Photo.PhotoID, Photo.Title, Rating.PhotoNO, Rating.Rating, Photo.url FROM [Photo] RIGHT JOIN [Rating] ON Photo.PhotoID = Rating.PhotoNO", cs, adOpenDynamic)

 

[PHV]

You want an average for which groups ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jamiehook01]

I want an average for the rating it is the rating field in the rating table

 

[PHV]

So, I guess a photo may have many ratings and thus the answer to my previous question is: an average for each photo.
rs.Open("SELECT P.PhotoID, P.Title, R.PhotoNO, AVG(R.Rating) As RatingAverage, P.url" _
& " FROM [Photo] P RIGHT JOIN [Rating] R ON P.PhotoID = R.PhotoNO" _
& " GROUP BY P.PhotoID, P.Title, R.PhotoNO, P.url", cs, adOpenDynamic)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jamiehook01]

Sorry i was being stupid, that was exactly what i wanted.

Thank you very much