Select MAX date from multiple date fields?

[JPritch]

Is it possible to select 1 MAX date from multiple date fields for a record?

For example, on an employee record there are 10 date fields, each for a performance review date. Some employees may have date fields 1-3 with values, some with just 1, others 1-5, etc.....depending on how many performance reviews they've had.

Is there a way to pull the MAX review date for an employee, knowing that the MAX date could reside in Field 1 for a certain employee, and could be from Field 7 for another?

I appreciate the help guys!

 

[Pat Hartman]

Your problem is caused because you have created a repeating group. When attributes have a 1-many relationship with the identifier (employee in this case), then they should be stored as rows in a separate table. Once you correct the table structure problem, you can use the Max() function to return the data you want.

If you don't change the structure, you'll need to create a function into which you pass the 10 dates. It will need to compare them and return to you the highest value. Essentially, you are using spreadsheet techniques to design your tables. Do a search on normalization and spend some time reading to avoid future problems.