retrieve the first record of a set of records

[Mustanggt89]

I have a database the I need to retrieve the first occurance of a set of
records in Access 2003.

the database looks like

ID NUMBER DATE CREATED

200 05/10/2009
200 05/05/2009
200 05/02/2009
300 05/10/2009
300 05/05/2009
300 05/02/2009


I want to return
200 05/10/2009
300 05/10/2009

It is sorted by ID NUBMER (ascending) and Date created (descending). I did
this before but I can not rember how I did it


Thanks for any help.

David

 

[Duane Hookom]

I think you mean "table" rather than "database". I expect you want something
like:

SELECT [ID NUMBER], Max([DATE CREATED]) as MaxDate
FROM [Looks Like]
GROUP BY [ID NUMBER];

 

[John W. Vinson]

I have a database the I need to retrieve the first occurance of a set of
records in Access 2003.

the database looks like

ID NUMBER DATE CREATED

200 05/10/2009
200 05/05/2009
200 05/02/2009
300 05/10/2009
300 05/05/2009
300 05/02/2009


I want to return
200 05/10/2009
300 05/10/2009

It is sorted by ID NUBMER (ascending) and Date created (descending). I did
this before but I can not rember how I did it


Thanks for any help.

David

If this is ALL you need to do (i.e. no other fields) then change the query to
a Totals query. Group By [ID Number] and select Max of [Date Created].

If there is other data that you need to retrieve, you'll need a subquery
instead:

SELECT <whatever you want to see>
FROM table AS A
WHERE A.[Date Created] = (SELECT Max(B.[Date Created] FROM table AS B
WHERE B.[ID Number] = A.[ID Number])