Re: Find max date value in multiple columns

You should normalize your table structure. We don't have a clue what your
table structure is other than guessing. You could possible normalize your
table with a proper union query like:

SELECT DateFieldA as InspDate, "A" as InspType
FROM tblUnNormalized
WHERE DateFieldA is not Null
UNION ALL
SELECT DateFieldB, "B"
FROM tblUnNormalized
WHERE DateFieldB is not Null
UNION ALL
SELECT DateFieldC, "C"
FROM tblUnNormalized
WHERE DateFieldC is not Null
----
UNION ALL
SELECT DateFieldX, "X"
FROM tblUnNormalized
WHERE DateFieldX is not Null;

You can then find the maximum date by querying the union query.

--
Duane Hookom
MS Access MVP


<Eric.Haflett@xxxxxxxxx> wrote in message
news:1159387286.384085.230740@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I found this query that claims it can display the maximum (latest) date
in multiple columns containing dates. I have a table that contains
inspection dates for multiple reasons, each reason is a column with the
a date or null. I need a query that will tell me the last time the
item was inspected. Here's what I have so far...

SELECT ITEMNAME, (select max(dateValue)
from (select LiftEnd as dateValue
union all
select ManEnd
union all
select LiftQA
union all
select ManQA) as dates) AS MaxDateValue
FROM tblITEMS;

Can this query really do what I need? It seems to but I can't get it
to work. I'm working in MS Access 2000. Can anyone suggest a
fix/alternative?

Thank you!



.