Find the oldest date using a query

[siema24]

Hi, I'm wondering how to create a query that will filter data from a table based on the oldest date of apperance.
For example: I have a table tblCustomerDateComment there I put comments (many per one customer) to customers.


It consists of 3 fields:
- customerID (type: number)
- comment (type: text)
- date (type: date)

If it is filled with following data:

1, 'create a customer', 2014
1, 'customer stops to pay!', 2015
1, 'delete customer', 2016
2, 'create a customer', 1990
2, 'contact details updated', 1991

then I want my query to returns exact two rows (rows count = customers count):
1, 'create a customer', 2014
2, 'create a customer', 1990

do you guys know what statement should i put into "date" field criteria within a query to do it? thanks in advance!

[pbaldy]

Since you want the comment associated with the date, this type of thing is one way:

http://www.baldyweb.com/LastValue.htm

[orange]

I see Paul has answered.
Here is another

SELECT *
FROM Custcomments
WHERE commentdate IN (
        SELECT Min(Custcomments.CommentDate)
        FROM Custcomments
        GROUP BY Custcomments.CustId
        )

[pbaldy]

I see Paul has answered.
Here is another

SELECT *
FROM Custcomments
WHERE commentdate IN (
        SELECT Min(Custcomments.CommentDate)
        FROM Custcomments
        GROUP BY Custcomments.CustId
        )

I can't see how that would work with real data. Given expanded sample data, wouldn't it pull all comments with a 2014 or 1990 date?

[orange]

Yes. But I just responded given the data in the post.