Find for most recent date

[Raschelle]

Hi all,

I would like to set up a find on a db I have where it will find records with the most recent date. I have a basic database of training history, which contains CourseID, CourseName, NameFirst, NameLast, TrgDate. I would like to be able to find for each employee the most recent record for each CourseID. This will likely end up in a future script where managers could choose to view for one person or for everyone.

Thanks in advance to this wonderful community. :worship:

[jamesducker]

What you need here is a separate table of the employees.

Give each of them a unique employee ID number.

Take your existing table and delete the NameFirst and NameLast fields, put employeeID in there instead.

Then build a relationship from the employees table to your existing table, matching employeeID with employeeID.

You can then put a portal of training events on the employees page.

You can sort that relationship (and/or any portal based on it) in descending order of TrgDate.

This will get you nearly all the way to what you need. Come back if you've any questions and/or if you need further assistance -)

Hope that helps.

James

[Raschelle]

LOL...yes, there is actually that relationship set up pretty much what you described. What I'm trying to figure out how to do, in essence, is a report that shows for each employee only the most recent date that they received a training. I figured it would be done from the History db.

So, for example, employee 1 receives annual trainings on specific safety topics. The report for employee 1 would show for topic 1 only the record with the most recent training date, topic 2 (same), etc. Each topic only listed once on the report and only the most recent record (so if training dates are 2007, 2008, 2009, only 2009 record is on the report).

I'm probably making it more complicated than it has to be (I tend to over-think things), but being able to find/display a report for just one employee or all employees is the main goal.

Just for fun, I attached the current "test" files I've created to help figure this out.

Thanks.

emp_trg_test.zip

[jamesducker]

Okay, great. Then the job is simple.

Create a calc field on the employees table called LastTrainedDate... define it simply as

max (history::TrgDate)

Then do your export from the employees table, with the following fields:

FirstName

LastName

LastTrainedDate

Job done.

J

PS: I like your perpetually-gambolling cat.

[Raschelle]

I'm not sure that's it. I was playing with max yesterday, but I was getting only the max of all the trainings and not a max for each training course. Is there a way for the calculation to be set up to look at the max for a specific course (maybe a calc field for each course)? The report would like something like this:

John Doe

RTK Training xx/xx/xxxx (most recent date rec'd this trg)

Waste Training xx/xx/xxxx (ditto)

etc.

The employee would have several records for each training course in the history db and I need only the most recent from each topic for the report.

I hope that makes more sense. Thanks for your time on this.

(I love the cat too...it's one of the "more smilies" available for posting.)

[comment]

I would like to be able to find for each employee the most recent record for each CourseID.

I am afraid it's not simple at all. One way to handle this is to go to the history table and find records for the employees you want to include in the report. Sort the records by EmployeeID, CourseID and Date (ascending). Display them using a layout with sub-summary parts by EmployeeID and CourseID (trailing). Put the Date field in the sub-summary by CourseID part and delete the body part.

In version 9, you'll have to view the report in Preview mode or print it out.

---

P.S. Why do you use three separate files?

[comment]

See also:

http://fmforums.com/forum/showtopic.php?tid/212502/

[Raschelle]

Thanks Comment. I'll try the report setup you suggested and review that post as well. I had a feeling this one was probably way over my head, but I need to keep trying anyway, at least for today.

There are three files because there is other functionality to come later (this is just a test set to play with different ideas), such as noting when trainings are due or overdue and emailing to the manager and/or employee a reminder. The third db is for course information, such as frequency of training to assist with that, once I get to it.

[comment]

There are three files because there is other functionality to come later

IMHO, you're only making things more difficult for yourself.

[Raschelle]

:confused:

I would appreciate a suggestion on that too. Simpler sometimes is better. Would you just include the training info in the history or employee record (of how often a particular training should be done - once, annually, etc.)?

It's funny, I was reading the "sticky" post earlier about the newbies that start posts with "I'm new and my company asked me to...." and that's, of course, how I got started years ago (FM3 at the time). I love and appreciate all the time and effort the experts put into this forum for us who are just thrown into it w/o training. Today, I'm feeling like the developing should just be left to the developers.

[comment]

Would you just include the training info in the history or employee record

No, I think you are right to have three tables (Employees, Courses and Certifications) - I just think they could be in the same file.

About renewals: it depends if the required frequency of a course can ever change. If yes, than the renewal date must be stored in the Certification record - otherwise it can be calculated "live" using the related data from Courses.

[Raschelle]

I guess I misunderstood what you meant and used the wrong terminology. I do know they can be in the same file (one of my favorite of the updates since v3). But my boss, who started these files, apparently does not (lol).

The final solution will likely be many tables in one file.

Now, if it could just rank all the replies to my post, I would give them all 5 stars! (I click the "star" icon, but nothing happens).

Thanks to you and James I have figured out how to set it up to work the way the boss wants. Much appreciated!!