Query - First/Last on Group By doesn't work

[bo8482]

Hi
I've been trying to build a query from the following table:
ID|Client ID|Client Name|Client Type|Profitability|Last Rec'd Accounts
1 001 XYZ Ltd C 100000 30/04/09
2 001 XYZ Ltd C -40500 30/05/09
3 002 ABC Plc C 50000 01/04/09

Returning the results:
001 XYZ Ltd C -40500 30/05/09
002 ABC Plc C 50000 01/04/09

i.e. I want the query to return the last date of rec'd accounts when a profit figure has been entered (some months there may not be a profit figure entered).

I thought it would be a simple case of using Group by: LAST for profitability and last rec'd accounts but that didn't work. I have also tried MAX but that didn't work either.

This is my SQL:
SELECT tblClientDetails.ClientNumber, tblClientDetails.ClientManager, tblClientDetails.ClientName, tblClientDetails.ClientType, tblCovenants.Profitability, tblCovenants.DateAccountsPrep
FROM tblClientDetails LEFT JOIN tblCovenants ON tblClientDetails.ClientNumber = tblCovenants.ClientNumber
GROUP BY tblClientDetails.ClientNumber, tblClientDetails.ClientManager, tblClientDetails.ClientName, tblClientDetails.ClientType, tblCovenants.Profitability, tblCovenants.DateAccountsPrep
HAVING (((tblClientDetails.ClientType)="C"))
ORDER BY tblClientDetails.ClientNumber;

Can anyone help or give me any pointers?

Many thanks
Brian

 

[pbaldy]

Sounds like you need something like this:

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

 

[bo8482]

that certainly looks the ticket! now just a question of getting it to work....

thanks very much - will reply if i'm struggling!

Cheers
Brian