How do I select the earliest update of a set of records?

[Fibee]

Hi

This probably has a very simple answer but I can't find it! I have an audit table which records any changes made to another table which contains applications. The audit table records the date and time of the change, who did it and the application number. An application maybe updated a number of different times, what I want to do is just select the earliest update eg:

ID App no Who CHANGE_DATE
1 1234 Liz 28/07/1999 16:15:40 <
2 1234 Ann 28/07/1999 16:18:06
3 1234 Liz 28/07/1999 16:20:10
4 5432 Ann 29/07/1999 11:54:00 <
5 5432 Liz 29/07/1999 11:54:31
6 5432 Ann 29/07/1999 11:54:56
7 5432 Jane 29/07/1999 11:56:28


I only want to see IDs 1 and 4. Is there a function that would do this?

Thanks

Fi

 

[alvechurchdata]

Use a query. Put [App No] and [Change_Date] as fields and click on the "Totals" button in the toolbar. A new row will appear. Leave the [App No] column saying "Group By" but alter [Change_Date] to say "Min".

If you're doing it in program code you want something like:

SELECT [App No], Min([Change_Date]) AS FirstChange 
FROM Audit
GROUP BY [App No];

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Fibee]

Thanks for this - I did try the Min function but used the Values drop down on the tool bar and not the Totals icon. That seems to work, thanks.