[AccessD] SubQuery Help???

On 24 Jan 2004 at 20:17, Mark A Matte wrote:

> Hello All,
> 
> I'm having a brain cramp...I have a table with 3 fields: ID, Date, Amount.  
> I'm pulling the records between A-Date and B-Date...from these records I 
> need the date where the Amount is greatest...can someone refresh my 
> memory...
> 

Select ID,[Date] as HiDate,HiAmount
>From tblAmounts
Inner Join 
(Select Distinct ID, Max(Amount) as HiAmount
GroupBy By ID) As qMaxAmount
On tblAmounts.ID = qMaxAmount.ID And
tblAmounts.Amount = qMaxAmount,HiAmount

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.