Date query

[Stan]

I have a table that has two fields the first is the
commencement field and the second is the expiration field.
The record entry is listed below:


contract id type commence date expiration date
1 Lease 1/1/04 1/31/09
1 Amend 2/1/04 10/31/04

I want the query to find the earliest commence date and
the latest expiration.

The result would be 1/1/04 for the commencement and
1/31/09 for the expiration.

I tried using first and last function but the result was
not a 100% in all cases.

Is thier a date function I can use for this query?

 

[fredg]

I have a table that has two fields the first is the
commencement field and the second is the expiration field.
The record entry is listed below:


contract id type commence date expiration date
1 Lease 1/1/04 1/31/09
1 Amend 2/1/04 10/31/04

I want the query to find the earliest commence date and
the latest expiration.

The result would be 1/1/04 for the commencement and
1/31/09 for the expiration.

I tried using first and last function but the result was
not a 100% in all cases.

Is thier a date function I can use for this query?

Stan,

SELECT YourTableName.*
FROM YourTableName
WHERE YourTableName.[CommenceDate] =
DMin("[CommenceDate]","YourTableName") OR YourTableName.[ExpirationDate]
= DMin("[ExpirationDate]","YourTableName");

Should return earliest records for both the commence and expiration
dates.

 

[Chris]

Try ...

Select [Contract id], Min([C date] as Start, max(E date)
as Finish from YourTable Group by [Contract ID];

 

[Tom Ellison]

Dear Stan:

Forget First and Last. Use MIN() and MAX().

SELECT MIN([commence date]) AS EarliestCommencement,
MAX([expiration date]) AS LatestExpiration
FROM YourTableNameGoesHere

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts