I've no way of knowing. I inherited the "database" from an old DOS system. There are different databases for different bus regions, and the exe is duplicated in each of their folders, thus pointing at a different set of data each time. It's my understanding that operators' data are not to be out put if they don't have an RF1 record.
>Do you have any operators in the NET table that don't have any records in the RTF1 table?
>
>>I was getting duplicate hits of operator/route combinations.
>>
>>>What exactly is wrong with result of the query?
>>>
>>>>
>>>>No, that didn't work. I seem to have cracked it with:
>>>>
>>>>...
>>>> LEFT JOIN RF1 ;
>>>> On NET.Operator == RF1.Operator ;
>>>> and ( NET.Route == RF1.ERoute ;
>>>> or EMPTY( RF1.ERoute) ) ;
>>>>...
>>>>
>>>>
>>>>>>Sorry Sergey, I don't understand. I'm not intending to do "strange experiments". Your reply is very vague. I told you originally had that clause in, and got wrong results, and this was before I was advised to LEFT JOIN.
>>>>>>
>>>>>>Is there a solution to this or is the whole thread too hard to understand?
>>>>>Try
>>>>>
>>>>>SELECT DISTINCT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ;
>>>>> IIF( NET.Route == NET.Mcl_Rte, " ", NET.Mcl_Rte) as ContrNum, NET.Family, ;
>>>>> 0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ;
>>>>> NVL(RF1.ServType, "?") as ServeType ;
>>>>> FROM NotETM NET ;
>>>>> JOIN Operator op ;
>>>>> On NET.Operator == op.Code ;
>>>>> LEFT JOIN RF1 ;
>>>>> On NET.Operator == RF1.Operator ;
>>>>> and NET.Route == RF1.ERoute ;
>>>>> WHERE ( ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ; ...
>>>>>
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.