Re: Staff missing report

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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: bypassing some of the parameters in a parameter query
    ... >> Well, WHERE does work in design view, but having a lot of ANDs and ORs ... what is simple and direct logic in SQL View ... >> query to design view and see if it makes sense to you. ... >> Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: Future of ADPs
    ... To get there from the main window of SQL EM, click on Tools, then SQL Query ... Regardless of how I feel about it as a design tool, ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Designing Query - Help Please ASAP
    ... The changes made to the SQL of the query result from switching into design ... SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing ...
    (microsoft.public.access.queries)
  • Re: Expression Left in query giving syntax error
    ... trying to change focus from the field of the design grid. ... Next I tried coding the whole SQL expression including the Left function ... directly in the SQL window. ... The query is made of only one single table - so no ambiguities possible. ...
    (comp.databases.ms-access)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)