Query for Earliest date across 3 columns

  • Thread starter Thread starter HB
  • Start date Start date
H

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.
 
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]
 
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 ...
Click to expand...



This works OK if there are no null fields.



Douglas J. Steele said:
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



HB said:
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.
Click to expand...
Click to expand...
 
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



HB said:
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 ...
Click to expand...



This works OK if there are no null fields.



Douglas J. Steele said:
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



HB said:
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
Click to expand...
for
each
along with that heading?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top