L
lynn atkinson
I am working on a database which tracks staff training. I want to produce a
report which shows what training courses a person has attended and what
courses they are booked on. I have a table called bookings which shows what
eventID (course running on a particular date) a person is booked on. When
that person attends the course the status is changed from 'booked' to
'attended' (or not attended as the case may be).
However, many of the courses have to be refreshed annually. I only want to
show the last time a person went on that course. For example, First aid has
to be refreshed every year. I want to show only the LAST first aid course a
particular person went on - not a history of all first aid courses.
I have this working on one report using the Max function on the event date.
However, I am trying to produce a similar report in a different format, but
it is showing more than once for some people. The only thing I can see which
may be causing this is that the 'status'. In one entry the status is
attended, on the other entry, the status is booked.
How do I just show the 'booked' entry?
The sql so far is as follows
SELECT employeedetails.[employee ID new], employeedetails.surname,
employeedetails.forename, employeedetails.title, [post details].[post/role],
[Project Codes].Project, [Project Codes].locality, bookings.status,
Max([event schedule].eventdate) AS MaxOfeventdate, [course details].[course
title], [course details].[course category], [course details].[course title],
[course details].[qualification duration], CVDate(IIf([qualification
duration]=0,Null,Max((DateAdd("m",[course details]![qualification
duration],[event schedule]![eventdate]))))) AS [calc date due]
FROM [course details] RIGHT JOIN ((bookings LEFT JOIN [event schedule] ON
bookings.[event ID] = [event schedule].[event ID]) RIGHT JOIN ([Project
Codes] RIGHT JOIN ([post details] RIGHT JOIN employeedetails ON [post
details].[post ID] = employeedetails.[post ID]) ON [Project Codes].Code =
[post details].[project code]) ON bookings.employeeID =
employeedetails.[employee ID new]) ON [course details].[course code] = [event
schedule].[course code]
GROUP BY employeedetails.[employee ID new], employeedetails.surname,
employeedetails.forename, employeedetails.title, [post details].[post/role],
[Project Codes].Project, [Project Codes].locality, bookings.status, [course
details].[course title], [course details].[course category], [course
details].[course title], [course details].[qualification duration]
HAVING ((([Project Codes].Project)=[enter project]));
Can anyone point me in the right direction?
report which shows what training courses a person has attended and what
courses they are booked on. I have a table called bookings which shows what
eventID (course running on a particular date) a person is booked on. When
that person attends the course the status is changed from 'booked' to
'attended' (or not attended as the case may be).
However, many of the courses have to be refreshed annually. I only want to
show the last time a person went on that course. For example, First aid has
to be refreshed every year. I want to show only the LAST first aid course a
particular person went on - not a history of all first aid courses.
I have this working on one report using the Max function on the event date.
However, I am trying to produce a similar report in a different format, but
it is showing more than once for some people. The only thing I can see which
may be causing this is that the 'status'. In one entry the status is
attended, on the other entry, the status is booked.
How do I just show the 'booked' entry?
The sql so far is as follows
SELECT employeedetails.[employee ID new], employeedetails.surname,
employeedetails.forename, employeedetails.title, [post details].[post/role],
[Project Codes].Project, [Project Codes].locality, bookings.status,
Max([event schedule].eventdate) AS MaxOfeventdate, [course details].[course
title], [course details].[course category], [course details].[course title],
[course details].[qualification duration], CVDate(IIf([qualification
duration]=0,Null,Max((DateAdd("m",[course details]![qualification
duration],[event schedule]![eventdate]))))) AS [calc date due]
FROM [course details] RIGHT JOIN ((bookings LEFT JOIN [event schedule] ON
bookings.[event ID] = [event schedule].[event ID]) RIGHT JOIN ([Project
Codes] RIGHT JOIN ([post details] RIGHT JOIN employeedetails ON [post
details].[post ID] = employeedetails.[post ID]) ON [Project Codes].Code =
[post details].[project code]) ON bookings.employeeID =
employeedetails.[employee ID new]) ON [course details].[course code] = [event
schedule].[course code]
GROUP BY employeedetails.[employee ID new], employeedetails.surname,
employeedetails.forename, employeedetails.title, [post details].[post/role],
[Project Codes].Project, [Project Codes].locality, bookings.status, [course
details].[course title], [course details].[course category], [course
details].[course title], [course details].[qualification duration]
HAVING ((([Project Codes].Project)=[enter project]));
Can anyone point me in the right direction?