>>>>How can I build a single record parent-child view with all the child values coming from the same record? The child record should supply date, amount and otherfield values, but all of them from the record with max(date). Using max(amount) and max(otherfield) gives values that aren't what I need. For this example, I need 6/1/2004, $90.00, Abacus.
>>>>

>>>>Date        Amount   Otherfield
>>>>01/10/2004  $115.00  Zephyr
>>>>06/01/2004  $90.00   Abacus
>>>>

>>>
>>>SELECT * FROM Table WHERE Date = (SELECT MAX(Date) FROM Table) INTO ....
>>>
>>>Does this what you want?
>>
>>Not quite. While this gets the record, I need it in a parent-child join and can't get it to work right.
>>
>>CREATE SQL VIEW AS Select parent.name, parent.address, max(child.date), max(child.amount), max(child.otherfield) ;
>>FROM mydbc.parent INNER JOIN mydbc.child on parent.parentid = child.parentid ;
>>GROUP BY 1, 2
>
>
>

>SELECT Child.date, Child.Amount, Child.OtherField,;
>       Parent.Name, Parent.Address,Child.ParentId;
>FROM mydbc.Child;
>LEFT JOIN mydbc.Parent ON Child.ParentId = Parent.Parentid ;
>WHERE Child.date IN (SELECT MAX(Child.date) FROM Child));
>GROUP BY Child.ParentId
>

This is getting me all parent and child records with the max(date), so it's closer than I was.
But I need all parent records with the (different) max(child.date) for each parent. Thanks for the help, I'm going to keep working on it.