Sergey;

So, you think the inner SELECT can use a column from the outer select in it's WHERE? That would be unexpected...

>Try

SELECT a.acctnum, a.officername,
>	b.anotherfield, b.anotherfield2,
>	c.datefield, c.amountfield
>FROM a
>INNER JOIN b on a.AcctNum = b.AcctNum
>INNER JOIN c ON a.acctnum = c.acctnum
>	AND c.datefield = (SELECT max(datefield) FROM c WHERE AcctNum=a.AcctNum)
>

>
>>I need a join between a parent table and a child (1-m) returning several child fields - but the child fields must be for only the most recent record.
>>
>>If I just wanted the date field, it would be an easy MAX and GROUP BY - but since I want mutliple fields from the child it seems more difficult.
>>
>>I think I need something like:
>>
>>SELECT a.acctnum, a.officername, b.anotherfield, b.anotherfield2, c.datefield, c.amountfield
>>FROM a
>>INNER JOIN b on a.AcctNum = b.AcctNum
>>INNER JOIN c ON a.acctnum = c.acctnum
>> AND datefield=(SELECT max(datefield) FROM c WHERE c.AcctNum=____)
>>
>>... but I dont think the sub-select has any way of knowing the acctnum of the current row in table a in the parent select...
>>
>>How can I best accomplish this?
>>
>>TIA