Re: Staff missing report

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" <NoviceIan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8A79ECAE-04F2-4796-9EF2-E103BB500E0B@xxxxxxxxxxxxxxxx
> 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.
>
> "John Spencer" wrote:
>
>> 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
>>
>>
>> "NoviceIan" <NoviceIan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:D6F3B178-E6AC-497E-90B9-B84FB0F73606@xxxxxxxxxxxxxxxx
>> > 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.
>> >
>> >
>> > "John Spencer" wrote:
>> >
>> >> 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?
>> >>
>> >> "NoviceIan" <NoviceIan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:D831ADD3-1477-46EB-8082-0D9636EB3686@xxxxxxxxxxxxxxxx
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Staff missing report
    ... Please copy and post the SQL of your query. ... > Interview Date LAST ... We have a staff table and an Interview table. ... >>> review we complete an interview form which populates the underlying ...
    (microsoft.public.access.reports)
  • Re: Staff missing report
    ... FROM Interview as T ... >> I would write the query as the following UNTESTED query. ... sorting in the query is more or less useless for reports. ...
    (microsoft.public.access.reports)
  • Re: Counting yes values only in query
    ... your query and add in more fields. ... Select Totals from the View menu ... don't forget to tell us what type of field [Initial Interview] is? ... > Sorry to be a pain about this but I get an error message ...
    (microsoft.public.access.queries)
  • Basic Security Steps
    ... Even I know all the different anserwers for the questions, ... still I wanted the best one for this query. ... asked in an interview recently... ... Dan! ...
    (microsoft.public.sqlserver.security)
  • Re: Forms Design and Layout help please
    ... rest of the cats details. ... when I call up the [fullname] from the query it dosnt go in. ... arent in the table/query I select in the wizard. ...
    (microsoft.public.access.gettingstarted)