Unanswered: Query to establish most recent, by date, occurrence of a type of training per person
Hello hope someone can help with the following.
Have a staff training database which records amongst other things the names and dates of courses that people have been on. There are 2 courses that I want to query, Epilepsy and Epilepsy Refresher. When someone has done the Epilepsy course they need to do the Epilepsy Refresher course after 12 months. If they fail to do the Refresher course within say 2 years they need to start again and take the initial Epilepsy course I want to know what is the most recent course of either those two that people have done.
Then having established that say Person A has done the courses several times but the most recent course was say Epilepsy I need to only output the most recent course and date then apply a date comparison with the date of that course and 'today' to see when that course needs to be retaken. Output the date that the course needs to be refreshed and if its overdue by a given amount ( say 12 months ) highlight that record.
So have related tables for people and courses. I can filter all the training records using the Like (Epilepsy*) expression that gives me all the people who have done any kind of Epilepsy course and lists all such course done per person. First off how do I limit that to just the most recent course per person.
Then having got that single Person- Course Name- Date need to apply an expression that looks at the single Course Name and applies the appropriate rule to the date comparing it to 'Now' and outputting when teh course needs to be refreshed.
eg one record shows
Name Course Name Date of Course
Fred Smith - Epilepsy - 12/08/2009 I look at the course name 'Epilepsy' , I know that a Refresher Course should follow after say 12 months , I look at the date taken ' 12/08/09' and therefore add 12 months and output into 2 new fields
Course Name Date To Be Done
Epilepsy Refresher 12/08/2010
Hope that makes sense ? Am a novice/moderate user so would appreciate any help offered to treat me as such. Have honestly tried to work it out, Google , search this forum etc but can't find a near enough match.
Same database and what should be a simpler query but again can't find the answer I need to output a list of people who haven't done any Epilepsy training, Using a query with Not Like " Epilepsy* gets me such a list but it is all the training that people have done which isn't Like " Epilepsy* so several records and therefore instances of people's names. I just need 1 record
Name - Workbase per person for people have not done any epilepsy training?
Many thanks for any help
In respect of second part of my post on how to get only the record with the most recent date I have tried the 'max' function. This still gives all the records. Works if I reduce the fields in the query to just the persons name and the Date Completed field. I get the correct, most recent date but don't then have the course name.
Works if I reduce the fields in the query to just the persons name and the Date Completed field. I get the correct, most recent date but don't then have the course name.
Perhaps this is a table design fault? If you link the courses table to the CourseID which should be in the same table as the date attended, you should get the course name without affecting anything else.
It would be so much easier if I could see your table structure and your query design.
Paul thanks , your solution worked , just needed a prior step to filter for the 'epilepsy' courses to make a new table to run the queries on.
Now need to work out the if statements to produce a Due/Overdue/NotDue return from the Date Completed field depending on which of the two types of courses was most recently completed. Will have a go.
No problem, glad it helped. I would have thought a criteria could have been used instead of a new table, but don't know that much about your situation. Post back if you get stuck on the if statements (check out the Switch function as well).