Query to show the latest action taken.

[hooi]

If I have a table containing the following four fields (dashes added in place of spaces to make it easier to see)

ID--Foreignkey-------When-------Action_Taken
1---5---------------6/12/2003-----action1
2---5---------------6/13/2003-----action2
3---5---------------6/14/2003-----action3
4---6---------------6/15/2003-----action1
5---6---------------6/16/2003-----action2

and I want to create a query to only show the latest actions taken for each foreign key, ie. for ID 3 and 5 to be displayed only. How can this be done?

Thanks for helping.

 

[samehkh]

This can be achieved easily using an aggregate query choosing the "group by" field to be the foreign key and then choose max of [ID] that way for every foreign key you get the last transaction.

 

[namliam]

And maybe adding a Having cluase on having max(([Id])

Regards

 

[hooi]

Thanks...