Query for Earliest date across 3 columns

[HB]

I'm trying to create a query that will return the earliest date among 3
dates in 3 different columns and return the titles of the columns the same.

For example,

Name First Date Second Date Third Date
Smith Inc. 03/23/99 08/23/03 03/12/03
Smith Inc. 12/20/01 10/22/02 11/28/99

Is there a way to do this with queries and return the earliest date for each
along with that heading?

Thanks.

 

[Douglas J. Steele]

Easiest way would be to normalize your table so that you don't have 3 date
fields in one row, but rather have 1 date field in 3 rows. You can
accomplish that using a UNION query:

SELECT Name, "First Date" AS Comment, [First Date]
UNION
SELECT Name, "Second Date" AS Comment, [Second Date]
UNION
SELECT Name, "Third Date" AS Comment, [Third Date]

 

[HB]

Hi Doug, Thanks. I figured out how to do it. The database is normalized. The
3 dates are for 3 different fields. Someone gave me a suggestion for using
IIF( ) in a query to select the earliest date as follows.


Select OrgName, iif(Date1 < Date2, iif(Date3 < Date1, Date3, Date1),

iif(Date3 < Date2, Date3, Date2)) From ...

This works OK if there are no null fields.

Easiest way would be to normalize your table so that you don't have 3 date
fields in one row, but rather have 1 date field in 3 rows. You can
accomplish that using a UNION query:

SELECT Name, "First Date" AS Comment, [First Date]
UNION
SELECT Name, "Second Date" AS Comment, [Second Date]
UNION
SELECT Name, "Third Date" AS Comment, [Third Date]


--
Doug Steele, Microsoft Access MVP

I'm trying to create a query that will return the earliest date among 3
dates in 3 different columns and return the titles of the columns the same.

For example,

Name First Date Second Date Third Date
Smith Inc. 03/23/99 08/23/03 03/12/03
Smith Inc. 12/20/01 10/22/02 11/28/99

Is there a way to do this with queries and return the earliest date for each
along with that heading?

Thanks.

 

[Douglas J. Steele]

I don't really understand how you could have 3 different dates on a single
normalized record and need to do such a comparison, but if it's legitimate,
you may need to wrap IsNull functions around your dates.

Without fully understanding what you want to do if date is null, I can't
offer concrete suggestions, I'm afraid.

--
Doug Steele, Microsoft Access MVP

Hi Doug, Thanks. I figured out how to do it. The database is normalized. The
3 dates are for 3 different fields. Someone gave me a suggestion for using
IIF( ) in a query to select the earliest date as follows.


Select OrgName, iif(Date1 < Date2, iif(Date3 < Date1, Date3, Date1),

iif(Date3 < Date2, Date3, Date2)) From ...

This works OK if there are no null fields.

Easiest way would be to normalize your table so that you don't have 3 date
fields in one row, but rather have 1 date field in 3 rows. You can
accomplish that using a UNION query:

SELECT Name, "First Date" AS Comment, [First Date]
UNION
SELECT Name, "Second Date" AS Comment, [Second Date]
UNION
SELECT Name, "Third Date" AS Comment, [Third Date]


--
Doug Steele, Microsoft Access MVP

I'm trying to create a query that will return the earliest date among 3
dates in 3 different columns and return the titles of the columns the same.

For example,

Name First Date Second Date Third Date
Smith Inc. 03/23/99 08/23/03 03/12/03
Smith Inc. 12/20/01 10/22/02 11/28/99

Is there a way to do this with queries and return the earliest date

for
each

along with that heading?

Thanks.