Staff missing report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
The tables are related using a one to many relationship but its from Staff to
interview. I did it this way based on: a staff member may have many
interviews.
 
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.
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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.

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Glad that relationship is correct thats all need right now is another
problem. The join field is Staff Number which is a five digit unique number.
Sorry I'm no good at SQL I usually use the design view to make queries, how
would I transfer the SQL?

Sorry and many thanks for your help

Ian
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top