syntax error (missing operator) in ms access

Question

I'm trying to use the following query, however it is saying missing operator and syntax error. This problem is with ms access.

select s.student_id, e.average_second_year_marks, e.average_third_year_marks, e.overall_marks
from students s
join
(
  select student_id,
    avg(case when program_year_when_enrolled = 'Second' then marks_obtained end)
      as average_second_year_marks,
    avg(case when program_year_when_enrolled = 'Third' then marks_obtained end)
      as average_third_year_marks,
    (
     (sum(case when program_year_when_enrolled = 'Second' then marks_obtained end) * 1.0) +
     (sum(case when program_year_when_enrolled = 'Third' then marks_obtained end) * 2.0)
    ) / 3.0 as overall_marks
  from enrollment
  group by student_id
) e on e.student_id = s.student_id
where s.course_current_status = 'Graduated-2017'

If this about Microsoft Access, why did you tag MySQL and Postgres then? I removed the tags. Please only add tags that are relevant. — sticky bit
– sticky bit, Commented Apr 29, 2019 at 19:54
MS Access doesn't support the 'CASE' statement, you will need to convert those into iif statements. — Fink
– Fink, Commented Apr 29, 2019 at 20:03

Gordon Linoff · Accepted Answer · 2019-04-29 20:14:27Z

1

The equivalent MS Access query would be more like this:

select s.student_id, e.average_second_year_marks,
       e.average_third_year_marks, e.overall_marks
from students as s inner join
     (select student_id,
             avg(iif(program_year_when_enrolled = "Second", marks_obtained, NULL)) as average_second_year_marks,
             avg(iif(program_year_when_enrolled = "Third", marks_obtained, NULL)) as average_third_year_marks
             (1.0 * avg(iif(program_year_when_enrolled = "Second", marks_obtained, NULL)) +
              2.0 * avg(iif(program_year_when_enrolled = "Third", marks_obtained, NULL)) 
             ) / 3.0 as overall_marks
     from enrollment
     group by student_id
    ) as e 
    on e.student_id = s.student_id
where s.course_current_status = "Graduated-2017";

answered Apr 29, 2019 at 20:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mehdi Over a year ago

It's saying syntax error in join clause but when I use another join such as a left join or outer join it then says syntax errors in join operation. Can you please help what I should do with it.

Collectives™ on Stack Overflow

syntax error (missing operator) in ms access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related