query for latest course

[lynn atkinson]

Our employees attend training courses which need to be refreshed. I have a
report which shows when a course refresher is due or overdue. It only shows
the last occurance of that course the person has attended (using Max in the
totals row in the query). However, if that person is already booked on a
forthcoming refresher I do not want them to show up as being due.

The report is based on a query as below.

SELECT bookings.employeeID, employeedetails.surname,
employeedetails.forename, bookings.status, [event schedule].[course code],
Max([event schedule].eventdate) AS MaxOfdate, [course details].[course
title], [course details].[qualification duration], bookings.explain,
CVDate(IIf([qualification duration]=0,Null,Max((DateAdd("m",[course
details]![qualification duration],[event schedule]![eventdate]))))) AS [calc
date due], [post details].location, [Project Codes].Project
FROM [course details] INNER JOIN ((((employeedetails INNER JOIN bookings ON
employeedetails.[employee ID new] = bookings.employeeID) INNER JOIN [event
schedule] ON bookings.[event ID] = [event schedule].[event ID]) INNER JOIN
[post details] ON employeedetails.[post ID] = [post details].[post ID]) LEFT
JOIN [Project Codes] ON [post details].[project code] = [Project Codes].Code)
ON [course details].[course code] = [event schedule].[course code]
GROUP BY bookings.employeeID, employeedetails.surname,
employeedetails.forename, bookings.status, [event schedule].[course code],
[course details].[course title], [course details].[qualification duration],
bookings.explain, [post details].location, [Project Codes].Project
HAVING (((bookings.status)<>"booked" And (bookings.status)<>"provisional")
AND ((CVDate(IIf([qualification duration]=0,Null,Max((DateAdd("m",[course
details]![qualification duration],[event
schedule]![eventdate]))))))<Date()+90) AND (([Project Codes].Project)=[enter
project]))
ORDER BY Max([event schedule].eventdate);

Where and How do I not show a course if the person is booked on a
forthcoming course?

As an example
first aid course have to be refreshed every 12 months. The report shows when
a person is due the next course. It only shows the last first aid course the
person attended - not all the first aid courses the person has ever been on.
I want to omit first aid on the report if that person is booked on a first
aid course in the future.

Hope all this makes sense. Any ideas?

cheers

 

[Guest]

hi,
confused here. in your query you have
HAVING (((bookings.status)<>"booked"
seems to me that should be eliminating those who have been
booked.
do you make an entry if someone is booked?

-----Original Message-----
Our employees attend training courses which need to be refreshed. I have a
report which shows when a course refresher is due or overdue. It only shows
the last occurance of that course the person has attended (using Max in the
totals row in the query). However, if that person is already booked on a
forthcoming refresher I do not want them to show up as being due.

The report is based on a query as below.

SELECT bookings.employeeID, employeedetails.surname,
employeedetails.forename, bookings.status, [event schedule].[course code],
Max([event schedule].eventdate) AS MaxOfdate, [course details].[course
title], [course details].[qualification duration], bookings.explain,
CVDate(IIf([qualification duration]=0,Null,Max((DateAdd ("m",[course
details]![qualification duration],[event schedule]! [eventdate]))))) AS [calc
date due], [post details].location, [Project Codes].Project
FROM [course details] INNER JOIN ((((employeedetails INNER JOIN bookings ON
employeedetails.[employee ID new] = bookings.employeeID) INNER JOIN [event
schedule] ON bookings.[event ID] = [event schedule]. [event ID]) INNER JOIN
[post details] ON employeedetails.[post ID] = [post details].[post ID]) LEFT
JOIN [Project Codes] ON [post details].[project code] = [Project Codes].Code)
ON [course details].[course code] = [event schedule]. [course code]
GROUP BY bookings.employeeID, employeedetails.surname,
employeedetails.forename, bookings.status, [event schedule].[course code],
[course details].[course title], [course details]. [qualification duration],
bookings.explain, [post details].location, [Project Codes].Project
HAVING (((bookings.status)<>"booked" And (bookings.status)

AND ((CVDate(IIf([qualification duration]=0,Null,Max ((DateAdd("m",[course
details]![qualification duration],[event
schedule]![eventdate]))))))<Date()+90) AND (([Project Codes].Project)=[enter
project]))
ORDER BY Max([event schedule].eventdate);

Where and How do I not show a course if the person is booked on a
forthcoming course?

As an example
first aid course have to be refreshed every 12 months. The report shows when
a person is due the next course. It only shows the last first aid course the
person attended - not all the first aid courses the person has ever been on.
I want to omit first aid on the report if that person is booked on a first
aid course in the future.

Hope all this makes sense. Any ideas?

cheers
.