CODEWITHSUNDEEP
sqlms-access
user3105414
user3105414

Reputation: 31

MS Access SQL Average function with decimals

I am trying to create a rating system for an item and have the average of all of the ratings show for that item on a form. I want the rating to have 1 decimal place. I came up with the code below:

SELECT OvrRate.OvRating, *
FROM tbl1 LEFT JOIN 
    (SELECT Field1, ROUND(AVG(Rating),1) AS OvRating 
     FROM tbl_Review GROUP BY Field1)  AS OvrRate 
ON tbl1.Field1 = OvrRate.Field1;

So far, my code works in showing 1 decimal place, but when it is supposed to be 5.3 it shows 5.0 instead. All of the reviews are stored properly (I think). I have one review of 5.3 which means the average should be 5.3 as well, but like I said above, it is not querying this way.

Any fixes?

Upvotes: 0

Views: 1121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

MS Access does integer arithmetic on integers, so you need to convert the value. I find the easiest way is to multiply by 1.0:

SELECT OvrRate.OvRating, *
FROM tbl1 LEFT JOIN 
    (SELECT Field1, ROUND(AVG(Rating*1.0), 1) AS OvRating 
     FROM tbl_Review
     GROUP BY Field1
    )  AS OvrRate 
    ON tbl1.Field1 = OvrRate.Field1;

Upvotes: 1

Related Questions