>>>>
>>>>
>>>>In other words, all fields not listed in Group By should have some aggregate function. If these fields are memo fields, then you're out of luck.
>>>
>>>Thanks for your response. There are some memo fields included. So I have to add an other outer join to the query for these fields to solve it? I didn't think about this...
>>
>>Yes, you may try to re-write your select this way:
>>
>>select * from Table1 inner join Table2 on Table1.Pk = Table2.fk ;
>>inner join (select Pk, Min(date) as MinDate from Table2 group by 1) ;
>>MinDate on Table2.Pk = MinDate.Pk
>
>Naomi, if pk is a primary key, group by is useless
Ouch, you're right. I guess we need to exclude PK and do group by some other field and join using this field. Would it work?
If it's not broken, fix it until it is.
My Blog