All:

I have a Personnel table and a Attendance table. I want to join the two,
retrieving all employees but only specific attendance. The following query
works without error, but only gives me employees with matching attendance
records.

SELECT p.p_empno, p.p_fname, p.p_lname, a.a_date, a.a_reason, a.a_comments
FROM hrpersnl p LEFT JOIN
hattran a ON p.p_empno = a.a_empno
WHERE (p.p_active="A") and (a.a_date Between {d '2006-01-01'} And {d
'2006-01-14'})

To get a true left join, I used the following concept in Access, which
worked perfectly. I simply replace the joined table with a subquery and
moved the "where" clause. When I try it in MS Query, I get a "Syntax Error"
message.

SELECT p.p_empno, p.p_fname, p.p_lname, a.a_date, a.a_reason, a.a_comments
FROM hrpersnl p LEFT JOIN
(SELECT * FROM hattran a
WHERE (a.a_date Between {d '2006-01-01'} And {d '2006-01-14'})) a
ON p.p_empno = a.a_empno
WHERE p.p_active="A"


Is there a way I can make this concept work?

Thanks!

greg