Selection Criteria Help

[TimothyP]

I have an employee table something like this:

Emp# Name Grade Effective Date
1001 Tom Baker G2 06/04/2002
1002 Judy Smith 9L 05/19/2003
1003 John Phillips D4 02/12/2001
1003 John Phillips A7 10/01/2003
1004 Rob Thomas ZZ 04/23/2002

I am trying to write a formula that will select the latest employee record determined by the effective date. However, if an employee has a record with an effective date in the future (i.e. employee 1003), I want the record with the effective date that has already past. In other words, for employee # 1003 I want to select the record with 02/12/2001 effective date until 10/01/2003, at which point I want to select the record with the 10/01/2003 effective date.

Thanks in advance for the knowledge!
TimothyP

 

[synapsevampire]

Group the report by Emp#

Use the report->Edit selection formula->Group and place something like:

{Table.Date}=Maximum ({Table.Date}, {table.emp#})

Then in the report-Edit selection formula-Record and place:

{Table.Date} <= currentdate

-k

 

[TimothyP]

Thanks!

This seems to work. However, I had to modify it to group on last name instead of employee ID to retain the original sort.