First, this newsgroup is about ADP and SQL-Server and has nothing with doing
regular queries in Access.  You should ask this kind of question in a more
appropriate newsgroup such as m.p.access.queries.

Second, I really don't understand the purpose here of making a Right Join
between the tables tblConsultants and tblAppraisals.  In the case that you
would want to keep both of these two JOIN, you'll have to give an alias to
the table tblAppraisals the second time it's jointed.

Finally, as to your problem, you have two problems with your subquery here.
First, from your description of the problem, I suppose that you should use
the Not Exists statement instead of an Exists statement (or replace True
with False).  BTW, the value returned by the Exists() statement is already a
logical (True/False) value; so you don't have to make a final comparaison
with « = True » to use it in your filtering WHERE statement. Second, you
must etablish a relationship between the main query and the subquery;
something like:

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname] &
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname, tblAppraisals.AppraisalYr

FROM tblConsultants LEFT JOIN tblAppraisals ON tblConsultants.ConsultantID =
tblAppraisals.ConsultantID

WHERE tblConsultants.ConsultantStatus = "Current"
   And tblConsultants.StartDate < #4/1/2007#

   And Not Exists (
       SELECT ConsultantId FROM tblAppraisals
       WHERE tblAppraisals.AppraisalYr <> "2007/8"
           And tblAppraisals.ConsultantId = tblConsultants.ConsultantId
   )

ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];

Notice the « AND tblAppraisals.ConsultantId = tblConsultants.ConsultantId »
statement that I've added inside the subquery.