First, LAST is not necessarily going to give you the date of the latest
interview for the person. It will give you the value in the last record
in
retrieval order. You should be using Max to get this date.
I would write the query as the following UNTESTED query.
SELECT Fullname.Expr1, Interview.InterviewDate, Interview.[Interview
Type]
FROM [District Nurse] LEFT JOIN (Fullname LEFT JOIN Interview
ON Fullname.[Staff Number]=Interview.[Staff Number])
ON ([District Nurse].[Staff Number]=Fullname.[Staff Number])
AND ([District Nurse].[Staff Number]=Interview.[Staff Number])
WHERE [District Nurse].[Staff Type] In
("Qualified","Unqualified","Admin")
AND Interview.[Interview Type]="PRPD"
AND Interview.[Interview Date] =
(SELECT Max(T.[Interview Date])
FROM Interview as T
WHERE T.[Staff Number] = [District Nurse].[Staff Number])
OR Interview.[Staff Number] is Null
By the way, sorting in the query is more or less useless for reports.
You
need to use the reports sorting and grouping property (Select View:
Sorting
and Grouping from the report design menu).
NoviceIan said:
SELECT Fullname.Expr1, Last(Interview.[Interview Date]) AS
[LastOfInterview
Date], Interview.[Interview Type]
FROM [District Nurse] INNER JOIN (Fullname INNER JOIN Interview ON
Fullname.[Staff Number]=Interview.[Staff Number]) ON ([District
Nurse].[Staff
Number]=Fullname.[Staff Number]) AND ([District Nurse].[Staff
Number]=Interview.[Staff Number])
WHERE ((([District Nurse].[Staff Type]) In
("Qualified","Unqualified","Admin")))
GROUP BY Fullname.Expr1, Interview.[Interview Type]
HAVING (((Interview.[Interview Type])="PRPD"))
ORDER BY Last(Interview.[Interview Date]);
Expr1 Is the concatenated fullname field.
:
Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
Hi,
I've already tried changing the join in the query with no luck.
Fullname (Query which concatenates forename and surname)
Interview Date LAST (Interview Table)
Interview Type [Criteria: Performance Review] (Interview Table)
Staff Type [Criteria: Active] (Staff Table)
The fullname query is used frequently in the database to fuel lookup
fields
so we generally use it in queries as well instead of concatenating
in
each
query.
:
Yes it can be done. You probably just need to change the join in
the
query
and maybe the criteria.
Can you post the query you are using to generate the report?
Hi,
We have a staff database which we use to monitor staff's annual
performance
review. We have a staff table and an Interview table. When
staff
have
their
review we complete an interview form which populates the
underlying
interview
table.
We would like to create a report which will show the date of the
staff's
last performance review. At present the report we have developed
only
displays staff who have had a review. Staff who have not had one
(new
staff
etc) are not included as they have no record in the interview
table.
However we would still like them to be displayed on the report
with
a
blank
date. Is this possible?
Many thanks
Ian
