>HI,ALL
> Anyone know Access sql statement , use left join 3 tables, and group by , having sum function...
>How can write to work, thx?
>
>select distinct (c.candidate_no) FROM ((candidate as c left JOIN education as e ON c.candidate_no = e.candidate_no )
> left Join working as w ON c.candidate_no = w.candidate_no )group by c.candidate_no
> having sum(datediff('yyyy','01/01/' & w.from_year ,'01/01/' & w.to_year )) >= 1
>
>tablename: Candidate
>----------------------------------
>
>candidate_id char(key)
> candidate_no char
>
>tablename: Education
>----------------------------------
>education_id char(key)
>candidate_no char
>
>tablename: Working
>----------------------------------
>working_id char(key)
>candidate_no char
>from_year int
>to_year int

1. Remove DISTINCT from your query - you're already grouping by Candidate_No, so no need for DISTINCT.

2. Remove, for now, HAVING clause from your query as this one, I believe, is written incorrectly.

If you can get it run without having, let's concentrate on adding HAVING then - I don't think you can create dates this way mising variables and constants.

Try first to add having with constant dates to understand how to create date constants in Access. Once you get that, we will be able to proceed with variable HAVING.

So, first apply my 2 suggestions and get the simpler SQL to run.