Subquery after Left Join

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

Sorry to rephrase, but I've simplified the issue:

This works...
SELECT p_empno, a_empno
FROM hrpersnl LEFT JOIN hattran
ON p_empno = a_empno

This works in Access, but gives syntax error in Excel query...
SELECT p_empno, a_empno
FROM hrpersnl LEFT JOIN (Select * from hattran)
ON p_empno = a_empno

The only difference is the query after the join. Is this really a syntax
error, or just not allowed in MS Query!?

Thanks again!




"Pontificateur" wrote:

> 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
>