MS Access Typecasting Number to Double

Question

I've got a MS Access 2007 DB with records of passed and failed attempts to pass an exam.

Student_id, Course_id, passed
S001        C001       0
S001        C001       1
S002        C001       1
S003        C001       0

'Passed' is used as a boolean where 0 is failed an 1 passed, but is stored as a number.

I want to build a query displaying the number of attempts per student per course. This can be done by averaging the number passed. S001 has an average of 0.5, S002 of 1 and S003 of 0. This would indicate that S001 passed after 2 attempts, S002 after 1, and S003 never made it.

SELECT Student_id, Course_id, avg(passed) 
FROM tbl 
GROUP BY Student_id, Course_id, passed

The problem is: the average's are all 0 or 1. My guess is that the number does not convert to a double (allowing for decimals). How do I cast the average in to a datatype allowing decimals?

Answer 1

I want to build a query displaying the number of attempts per student per course.

Use:

  SELECT student_id,
         course_id,
         COUNT(*) AS num_attempts
    FROM TBL 
GROUP BY student_id, course_id

I don't see where the average does anything meaningful. If you wanted the students passing percentage:

    SELECT t.student_id,
           t.course_id,
           COUNT(*) AS num_attempts,
           x.num_passed / COUNT(*) AS pass_percentage
      FROM TBL t
 LEFT JOIN (SELECT student_id,
                   course_id,
                   COUNT(*) AS num_passed
              FROM TBL
             WHERE passed = 1
          GROUP BY student_id, course_id) x ON x.student_id = t.student_id
                                           AND x.course_id = t.courseid
  GROUP BY t.student_id, t.course_id

Answer 2

I don't see the problem. I created a test table with the same data, and when I run this SQL:

  SELECT Student_id, Course_id, avg(passed) 
  FROM tbl 
  GROUP BY Student_id, Course_id

I get this result:

Student_ID  Course_ID   AvgOfPassed
      S001       C001   0.5
      S002       C001   1
      S003       C001   0

The SQL you original posted should produce a row for every row in your original table, since you mistakenly included PASSED in the GROUP BY.

My question for you is where you are viewing the data that you think it's producing the wrong results. It's quite clear that with the data you've provided and the description of the desired results, Access is returning exactly what you asked for if you write the SQL correctly.