Searching on Summary fields?

[Scott Pon]

I'm working on a Training (Certificates) Database. Employees take a test, and every year have to retake the test for re-certification.

Table Classes

- Class Number

- Class Name

Table Employee

- Employee Number

- Employee Name

Table Training Records (as a join table)

- Class Number - Number

- Employee Number - Number

- Test Date - Date

- Results - Text - Pass / fail

- Recert Date - Calculation - Test Date +365days

- Recert Date Last - Summary - Maximum of Recert Date

From this info, I have to create reports for:

Past Due - where Recert Date Last < Current Date

I tried to do find mode on the Recert Date Last field, but you can't search on the summary field.

I'm stuck. Does anyone have any ideas on how to create a past due report?

I'll be available to respond to any follow-up questions. Thanks in advance.

[Scott Pon]

Hmm no responses. I must have a hard one... stump the forums!

I'm gonna try "getsummary" function to see if that works.

[Søren Dyhr]

I can't see why you need to search on the summary field, do it on the calc'field instead?

Explain yourself!

--sd

[Scott Pon]

I used a summary field to find the Maximum Recertification Date.

I only want the newest training record to show up on a Past Due Report. Not the previous training records.

[Scott Pon]

Perhaps an example will help clearify.

Training Records Table:

Class - Name - Test Date - Recertify Date

--------------------------------------------

ClassA - Scott - 03/26/2006 - 03/26/2007

ClassA - Scott - 03/26/2007 - 03/26/2008

Now I need a Past Due Report to say that Scott didn't recertify before 03/26/2008

How would you do that?

[comment]

Well, strictly speaking, you are missing a table. There should be a table of Requirements (a join of Courses and Employees) and a table of CoursesTaken (a child of Requirements). Then you could search a calculated field in Requirements = Max ( CoursesTaken::Date ).

However, you can wing it by defining a self-join relationship in your TrainingRecords table as:

TrainingRecords::Class = TrainingRecords 2::Class

AND

TrainingRecords::Name = TrainingRecords 2::Name

then a calculation field = Max ( TrainingRecords 2::Date ) is the field to search.

---

BTW, you should use EmployeeID instead of Name for your relationships. Names can be changed, not unique, or just misspelled. Same with classes - actually everything.

[Scott Pon]

Thanks I'll try your suggestions and get back here as soon as I can.

[Scott Pon]

IT WORKS! Thanks for all your help 'comment'