ACCESS 2007: Determine maximum value per row across multiple columns

Question

I have a table with multiple date columns per employee:

   Emp 1, Date1, Date2, Date3
   Emp 2, Date1, Date2, Date3

etc.

I would like to write a single query that returns to me the maximum date of the three dates available on a per-employee basis. I understand I need to write a UNION ALL query but I can't seem to get the syntax so that I get a max value per employee record. I keep getting the same value for all employees. I'm sure this is a stupid error, but it's driving me nuts. Any help would be most appreciated.

Answer 1

Would something like this do: (this was MSSQL2008 not sure if it will work in Acess)

Create Test Data

create table t1
    ( EmployeeId int,
      Date1  datetime,
      Date2  datetime,
      date3  datetime)

insert into t1 values (1, '2009-10-11', '2009-04-01', '2009-12-25')
insert into t1 values (2, '2009-10-24', '2009-04-03', '2009-12-19')

My Query

select case 
    when date1 > date2 and DATE1 > date3 then date1
    when date2 > date1 and date2 > date3 then date2
    when date3 > date1 and date3 > date2 then date3
    end as highest
    from t1

Answer 2

Will something like this work?

select
  EmployeeColumn,
  iff(iif(Date1 > Date2, Date1, Date2) > Date3, iif(Date1 > Date2, Date1, Date2), Date3)
from yourTable

Answer 3

Thank you for the prompt responses! Is there a way to do this without using an iif statement? I'd like to generalize it if at all possible, since there are several other date fields and it gets really unwieldy figuring out the IIF logic.

Also, I forgot to mention that any of those dates could be empty, and the IIF logic seems to return the NULL as the maximum value.

Answer 4

SELECT ID, Max(TheDate) AS MaxDate
FROM
(
   SELECT ID, Date1 AS TheDate
   FROM myTable
   UNION 
   SELECT ID, Date2 AS TheDate
   FROM myTable
   UNION
   SELECT ID, Date3 AS TheDate
   FROM myTable
)
GROUP BY ID

I know that this is not a clean solution.
But this is what you are looking for when you wrote of UNION.

EDIT: You could use UNION ALL instead of UNION. With above query, it will not make a difference in the output, I suppose.

Answer 5

If you don't want to restrict the solution to SQL, you could use my iMax() function:

  Public Function iMax(ParamArray p()) As Variant
  ' Idea from Trevor Best in Usenet MessageID [email protected]
    Dim i As Long
    Dim lngUBound As Long
    Dim v As Variant

    v = p(LBound(p))
    lngUBound = UBound(p)
    For i = LBound(p) + 1 To lngUBound
      If v < p(i) Then
         v = p(i)
      End If
    Next
    iMax = v
  End Function

I call it "Immediate Max()" on analogy from IIf(), Immediate If(), because it's intended for row-level processing within a query.