Re: Staff missing report
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Tue, 10 Jan 2006 08:24:14 -0500
Ok. Let's trouble shoot this one bit at a time. I think I messed up the
join so try the following query
SELECT Fullname.Expr1, Interview.[Interview Date], 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]
Does that work? If so, then add
WHERE [District Nurse].[Staff Type] In ("Qualified","Unqualified","Admin")
AND Interview.[Interview Type]="PRPD"
Does that work? If so, then add
AND Interview.[Interview Date] =
(SELECT Max(T.[Interview Date])
FROM Interview as T
WHERE T.[Staff Number] = [District Nurse].[Staff Number])
Finally, add this part.
OR Interview.[Staff Number] is Null
"NoviceIan" <NoviceIan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BFD8604F-750D-4014-B109-8AACB32F8C33@xxxxxxxxxxxxxxxx
> Hi,
>
> I not very good with SQL i usually use queries from the design view. I
> tried just pasting the code in and it give an error message saying
>
> "Join expression not supported"
>
> Sorry to be a pain.
>
> Ian
>
> "John Spencer" wrote:
>
>> 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
.
- References:
- Re: Staff missing report
- From: John Spencer
- Re: Staff missing report
- From: John Spencer
- Re: Staff missing report
- From: NoviceIan
- Re: Staff missing report
- From: John Spencer
- Re: Staff missing report
- From: NoviceIan
- Re: Staff missing report
- Prev by Date: Re: after update populate field from a query
- Next by Date: Re: How to hide group-header dynamically
- Previous by thread: Re: Staff missing report
- Next by thread: Re: Staff missing report
- Index(es):
Relevant Pages
|
