All,
I have run into another challenge. I have a list of training courses that have been completed. Some of these courses must be retaken every year. Although the actual listing of the completed courses would naturally appear as entered (chronologically), I used a query to simply sort by the course, employee, and date (descending date so the most recently completed training is at the top). I am interested in how I can include scripting and/or add a query to remove/ignore/overlook these unneeded out-of-date trainings - I only need for the most recent training 'by training course' and 'by employee' to appear. On the attached screenshot is an example of what I am trying to overcome - the highlighted row is the course that I do NOT need to examine. Please note that manually removing the older courses from the list when newer courses are completed is undesirable as the intent is to capture all training activity - from start to finish... plus, removing older courses when the course is retaken would take too much time.

I have tried several methods such as using nested 'For Each' statements and comparing one record of the recordset to the recordset immediately below it. In theory, this should work but I was unable to use two variables in an 'If' statement (i.e., if [$variable_x] = [$variable_y]....). I did try to do this using VBScript but I got strange and inconsistent results. I also tried to add scripting that will performed immediately after the sorting query was run (the intent was to put a mark in a checkbox denoting that where outdated training had been superseded by new training for the same course for the same person but, this too, did not work.

Does anyone have an idea for method that would allow the program to ignore the older courses when the same person has completed the same course more recently? In the example screenshot, only one course meets the criteria that I am trying to eliminate but in actual practice it could be many - for example, courses that must be repeated four times a year. In summary, I need to capture the most recent time each training course was completed by each employee.
Thanks,
Daryl G

Using your existing query, include a date range you wish your query to only see records between.

Take the date of the training course field away from the current system date. If days is less than 365, then <insert the rest of your existing query>

If date is more than 365 days.. delete, or do nothing. It's up to you.

Regards
James

James,
Some courses are only required one time whereas others may be required to be retaken annually. Along with that, some courses are required to be retaken quarterly and some have a life of five years... I already have written the code that takes the 'life' of each course into consideration and it works well with those courses that must be retaken on some sort of schedule. The problem I am experiencing is how can I get a list of only the most recent completion of all the courses? If a course has only been completed once by a person, it is a no-brainer because that is the answer; but in cases where a person has completed this course three times, I need to be able to capture the most recent time that it was completed irregardless of the 'life' of the course.

The purpose of this is to produce a report for all training that has currently expired or will be expiring in x number of days. It 'mostly' works now except, in cases where the same course has been completed by the same person on more than one occasion, I get all the completion dates and I only need the date of the last time it was done.

Can this be accomplished?
Daryl G

See attached file as an example.
Press the GO button and enter in 2004 when prompted.

Note that it only finds records between 2004 and 2013, but it skips the one I left blank, and doesn't see the ones before 2004.

You can use this idea to count days too. Check out the "datepart" function.

Is that what your looking for?

Regards
James

I just figured this one out... and I see that I am starting to develop some 'Brilliant Database logic'! My solution may not be the most elegant BUT it appears to work exactly how I needed it to.

This was especially challenging for me because 1.) I am very much new to Brilliant Database, 2.) the life of the course can be as little as one month or as long as five years (it varies from course to course) so the code must be dynamic, and 3.) only certain courses have the requirement to be repeated on some basis.

Since I only needed to see the most recent date of each training completed by an employee, I had to determine a way to capture the newest date for a course when an employee had taken the same course on multiple occasions (all completed training records are contained within a single folder). Getting the most recent date to the top per course per employee was easy enough by using a query to sort by course name, employee, and then date (descending). The challenge now was to ignore the earlier dates that the course had been completed. I did this by adding an additional (hidden) field to the form which was populated when the query was run - the field simply consisted of concatenating the employee's name and the course name. This new value gave me something to use as an unique field which I included in a second query. Running these two queries within the scripting pulled only the exact data (most recently completed date of training) that I was seeking. This synopsis is much more basic that what the total scripting does as only certain courses have to be retaken on some sort of recurring basis and the 'life' of the course varies from months to years, depending on the nature of the course itself... But, in the end, this solution appears to work.

Daryl G

[edit]James, I just checked out your scripting. This is certainly another method that I can include in my 'arsenal' of tools; however, each course may have a different 'expiration' date so this would not be able to pull the information that I was seeking. I really struggled with a method that would work for all the varied life cycles of the training courses. I certainly thank you for your help and ESPECIALLY for your earlier words of encouragement a few weeks ago when I was at the point of dropping Brilliant Database altogether! One day, hopefully one day soon I will be able to return the favor and answer a question for others here (yes, it will have to be a basic question, but a question answered nevertheless).[/edit]