Retreiving latest values

[reginold15]

I have a table Project. It has the following column:
Project ID, projectName, UpdateTime

The data in the table is as follows:
1, abc, 12-2-2009 01:10:00
1, abc, 12-2-2009 04:18:00
2, xyz, 17-7-2009 08:45:00
2, xyz, 17-7-2009 12:21:00

i want the result set to display the latest update project information based on the update time.
for the above example , it should display
2, xyz, 17-7-2009 12:21:00
1, abc, 12-2-2009 04:18:00

any help appreciated

 

[Galaxiom]

Firstly your Project table should contain the ProjectID and ProjectName.
It should be separate from a related table (eg UpdateInfo) that contains the ProjectID and UpdateTime. Including both the ID and Name in repeated records is a normalisation anomaly.

UpdateTime should be a Date/Time field. If not then convert it.

Create a query using with a join between the tables on ProjectID.
Right click the design grid in the query builder and turn on Totals to make it an aggregate query.
Set ProjectID and ProjectName to Group By and UpdateTime to Max.

Ideally one would use a subquery for the aggregate and then join back to the ProjectName. That is how other information is incorporated back into aggregate queries but it is not essential in this particular case.