Show those who do not satisfy a condition

[Sandy]

I have a database that records appraisal records for a group of people dating
back to 2001. Various details are registered including the appraisal date and
the year, the latter as 2001/2, 2002/3 etc. I want to extract the names of
those who did not have an appraisal in 2007/8, excluding those appointed
within the last 12 months. The closest I have got is attached below, but the
logic is still not correct because it is still picking up some people who
have had a 2007/8 appraisal. Any help much appreciated.

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 ((Exists (SELECT ConsultantId
FROM tblAppraisals
WHERE tblAppraisals.AppraisalYr <> "2007/8"
And tblAppraisals.ConsultantId = tblConsultants.ConsultantId
))=False))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];

 

[KARL DEWEY]

This uses two queries instead of subquery --
Sandy_A ---
SELECT tblAppraisals.AppraisalYr, tblAppraisals.ConsultantID
FROM tblAppraisals
WHERE (((tblAppraisals.AppraisalYr)="2007/8"));

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname] &
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname
FROM tblConsultants LEFT JOIN Sandy_A ON tblConsultants.ConsultantID =
Sandy_A.ConsultantID
WHERE (((Sandy_A.ConsultantID) Is Null));

 

[Sandy]

Thanks Karl. I'm sorry but I'm not familiar with this approach. I would like
to have a single query that provides the records for a report. Is it possible
to combine the two queries into a single query? Thanks again for your help.
Sandy

This uses two queries instead of subquery --
Sandy_A ---
SELECT tblAppraisals.AppraisalYr, tblAppraisals.ConsultantID
FROM tblAppraisals
WHERE (((tblAppraisals.AppraisalYr)="2007/8"));

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname] &
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname
FROM tblConsultants LEFT JOIN Sandy_A ON tblConsultants.ConsultantID =
Sandy_A.ConsultantID
WHERE (((Sandy_A.ConsultantID) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have a database that records appraisal records for a group of people dating
back to 2001. Various details are registered including the appraisal date and
the year, the latter as 2001/2, 2002/3 etc. I want to extract the names of
those who did not have an appraisal in 2007/8, excluding those appointed
within the last 12 months. The closest I have got is attached below, but the
logic is still not correct because it is still picking up some people who
have had a 2007/8 appraisal. Any help much appreciated.

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 ((Exists (SELECT ConsultantId
FROM tblAppraisals
WHERE tblAppraisals.AppraisalYr <> "2007/8"
And tblAppraisals.ConsultantId = tblConsultants.ConsultantId
))=False))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];

 

[KARL DEWEY]

I am not versed in using subqueries. I have to use two.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl. I'm sorry but I'm not familiar with this approach. I would like
to have a single query that provides the records for a report. Is it possible
to combine the two queries into a single query? Thanks again for your help.
Sandy

This uses two queries instead of subquery --
Sandy_A ---
SELECT tblAppraisals.AppraisalYr, tblAppraisals.ConsultantID
FROM tblAppraisals
WHERE (((tblAppraisals.AppraisalYr)="2007/8"));

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname] &
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname
FROM tblConsultants LEFT JOIN Sandy_A ON tblConsultants.ConsultantID =
Sandy_A.ConsultantID
WHERE (((Sandy_A.ConsultantID) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have a database that records appraisal records for a group of people dating
back to 2001. Various details are registered including the appraisal date and
the year, the latter as 2001/2, 2002/3 etc. I want to extract the names of
those who did not have an appraisal in 2007/8, excluding those appointed
within the last 12 months. The closest I have got is attached below, but the
logic is still not correct because it is still picking up some people who
have had a 2007/8 appraisal. Any help much appreciated.

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 ((Exists (SELECT ConsultantId
FROM tblAppraisals
WHERE tblAppraisals.AppraisalYr <> "2007/8"
And tblAppraisals.ConsultantId = tblConsultants.ConsultantId
))=False))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];