Another SQL Question

[cpals]

Maybe I should just make a general sql topic to post in. 🙂

In Access I'm trying to list a bunch of numbers in a crosstab form and it's giving me an error about my data has been truncated and won't display correctly and it doesn't work. Is there anyway to tell Access how many decimals to display of the number in an SQL statement? This is the query:

SELECT d.departmentno, d.departmentname, c.courseno, c.courselevel, s.term, (SUM (s.enrollment)/COUNT (crn)) as avgenrollment
FROM tbldepartment as d, tblcourse as c, tblscheduledclass as s
WHERE d.departmentno = c.departmentno AND c.courseno = s.courseno AND s.classmode = 'Main'
GROUP BY d.departmentno, d.departmentname, c.courseno, c.courselevel, s.term

The part messing up is the "(SUM (s.enrollment)/COUNT (crn)) as avgenrollment" because it's giving out some numbers that have very long decimals... anyway to shorten them?

Thanks.

 

[TheLogLady]

have you tried the ROUND scalar function?

ROUND(SUM(s.enrollment)/COUNT(crn)),n)

where n = number of digits right of decimal to which to round

 

[cpals]

Hey, thanks... I'll try that. I just tried: LEFT( (SUM (s.enrollment)/COUNT (crn)) as avgenrollment), 2) and that cuts the number off 2 digits from the left, which is fine for what I need. I'll ask the teacher which way he would like better.