Identifying those who have not been appraised

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I have a database to log annual appraisals. It holds details of each
appraisal, including date, and the year of the appraisal, recorded as
'2005/6', '2006/7' etc. I wish to identify all those who have not had an
appraisal in 2007/8. The SQL below, of course, does not just identify those
people who were appointed before 1 April 2007 and who have not been appraised
in 2007/8. How do I restict it to those people? Any help much appreciated.
Sandy

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname] &
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname, tblAppraisals.AppraisalYr
FROM tblAppraisers RIGHT JOIN (tblConsultants LEFT JOIN tblAppraisals ON
tblConsultants.ConsultantID = tblAppraisals.ConsultantID) ON
tblAppraisers.AppraiserID = tblAppraisals.AppraiserID
WHERE (((tblConsultants.ConsultantStatus)="Current") AND
((tblConsultants.StartDate)<#4/1/2007#) AND ((Exists (SELECT ConsultantID
FROM tblAppraisals
WHERE tblAppraisals.AppraisalYr<>"2007/8"))=True))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];
 
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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sandy said:
I have a database to log annual appraisals. It holds details of each
appraisal, including date, and the year of the appraisal, recorded as
'2005/6', '2006/7' etc. I wish to identify all those who have not had an
appraisal in 2007/8. The SQL below, of course, does not just identify
those
people who were appointed before 1 April 2007 and who have not been
appraised
in 2007/8. How do I restict it to those people? Any help much appreciated.
Sandy

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname]
&
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname, tblAppraisals.AppraisalYr
FROM tblAppraisers RIGHT JOIN (tblConsultants LEFT JOIN tblAppraisals ON
tblConsultants.ConsultantID = tblAppraisals.ConsultantID) ON
tblAppraisers.AppraiserID = tblAppraisals.AppraiserID
WHERE (((tblConsultants.ConsultantStatus)="Current") AND
((tblConsultants.StartDate)<#4/1/2007#) AND ((Exists (SELECT ConsultantID
FROM tblAppraisals
WHERE tblAppraisals.AppraisalYr<>"2007/8"))=True))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];
Click to expand...
 
THank you very much Sylvain. My apologies for choosing the wrong discussion
group.

Sylvain Lafontaine said:
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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sandy said:
I have a database to log annual appraisals. It holds details of each
appraisal, including date, and the year of the appraisal, recorded as
'2005/6', '2006/7' etc. I wish to identify all those who have not had an
appraisal in 2007/8. The SQL below, of course, does not just identify
those
people who were appointed before 1 April 2007 and who have not been
appraised
in 2007/8. How do I restict it to those people? Any help much appreciated.
Sandy

SELECT tblConsultants.MainSite, tblConsultants.ConsultantStatus, [Surname]
&
", " & [Title] & " " & [Initials] AS Name, tblConsultants.StartDate,
tblConsultants.Surname, tblAppraisals.AppraisalYr
FROM tblAppraisers RIGHT JOIN (tblConsultants LEFT JOIN tblAppraisals ON
tblConsultants.ConsultantID = tblAppraisals.ConsultantID) ON
tblAppraisers.AppraiserID = tblAppraisals.AppraiserID
WHERE (((tblConsultants.ConsultantStatus)="Current") AND
((tblConsultants.StartDate)<#4/1/2007#) AND ((Exists (SELECT ConsultantID
FROM tblAppraisals
WHERE tblAppraisals.AppraisalYr<>"2007/8"))=True))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];
Click to expand...
Click to expand...
 
Back
Top